CODEWITHSUNDEEP
sqlsql-serverdeclareserver-name
Samy Sammour
Samy Sammour

Reputation: 2465

Declare the server name as a variable in MS Sql Server 2016

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

Upvotes: 4

Views: 8134

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

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];

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81950

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)

Upvotes: 8

Related Questions