4

I have a big script file and I need to use it on another server. I need to edit the name of the server one time at the beggining using Declare so I can be able to use the same script on multiple servers only by changing the value of the variable.

something like that: 

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

SELECT * From @Quell.[Documents]

but it did not work.

how to do it? thank you

Samy Sammour
  • 2,298
  • 2
  • 31
  • 66
  • 2
    You can only do this with dynamic sql – HoneyBadger Nov 09 '16 at 12:55
  • You can't parameterize identifires in sql. You can either use dynamic sql or edit the file using a text editor that has a find and replace function so that you can change every occurence of the server name with a single click. – Zohar Peled Nov 09 '16 at 12:56
  • 1
    Here's a link to the [Microsoft dynamic SQL docs](https://msdn.microsoft.com/en-us/library/ms709342%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396). An alternative approach is to use [SSIS](https://msdn.microsoft.com/en-us/library/ms141026.aspx) to loop over your servers, executing the script againt each. – David Rushton Nov 09 '16 at 13:00
  • Possible duplicate of [T-SQL Declaring Connection String as a Parameter](http://stackoverflow.com/questions/40475582/t-sql-declaring-connection-string-as-a-parameter) – S3S Nov 09 '16 at 13:00
  • thank you very much – Samy Sammour Nov 09 '16 at 13:01

2 Answers2

8

Unfortunately macro substitution is not permitted in SQL Server, but you can use dynamic SQL.

Declare @Quell nvarchar(100)
SET @Quell = '[server1].[dbo]'

Declare @SQL varchar(max) 
SET @SQL = 'SELECT * From ' + @Quell +'.[Documents]'
Exec(@SQL)
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
3

If you are running the script from SQL Server Management Studio, you can use a SQLCMD variable and run the script in SQLCMD mode (Query-->SQLCMD Mode). A SQLCMD script can also be executed using the SQLCMD command-line utility with the variable value(s) passed as command-line arguments.

Sample script:

:SETVAR Quell "[server1].[dbo]"

SELECT * From $(Quell).[Documents];
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71