patternsqlMinor
SQLCMD Mode in SSMS and @@variable expansion
Viewed 0 times
ssmsexpansionmodesqlcmdandvariable
Problem
When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL variables.
Problem definition
I want to use the power of SQLCMD's variable expansion to substitute environment specific values in our deploy scripts instead of the existing tsql string building mash I've walked into. With the one exception of current environment, the use of SQLCMD to handle deploys has gone extremely well.
And that generates the following results.
Meat Loaf says 2 out of 3 ain't bad, but I'd rather have a 3 out of 3 solution.
When that script gets deployed the test server, I don't want to trust the deployment folks with editing the script.
If the only solution to using SQLCMD is to invoke scripts completely from the command line, I can accept that but wanted to throw this out here as I'm green to using SQLCMD.
Desired output
Results
Fruitless pursuits
The first BOL link showed promise, all I had to do was use the SQLCMDSERVER but to no avail. Run within the context of SSMS in SQLCMD mode, it will throw a fatal scripting error
Tumbleweed update
2011-08-12
In an attempt to reduct my problem to the simplest form, based on the answers, I oversimplified my queries (my apologies). A portion of the query I used is below. The two reponders are correct in their answers th
Problem definition
I want to use the power of SQLCMD's variable expansion to substitute environment specific values in our deploy scripts instead of the existing tsql string building mash I've walked into. With the one exception of current environment, the use of SQLCMD to handle deploys has gone extremely well.
--
-- define 2 sqlcmd variables that will be expanded in scripts
--
:setvar dbServer "DEVA2\DEV2"
:setvar dbNotServer @@servername
SELECT
'$(dbServer)' AS hard_coded_value
, @@servername AS [servername]
, '$(dbNotServer)' AS dbNotServerAnd that generates the following results.
hard_coded_value servername dbNotServer
DEVA2\DEV2 DEVA2\DEV2 @@servernameMeat Loaf says 2 out of 3 ain't bad, but I'd rather have a 3 out of 3 solution.
When that script gets deployed the test server, I don't want to trust the deployment folks with editing the script.
If the only solution to using SQLCMD is to invoke scripts completely from the command line, I can accept that but wanted to throw this out here as I'm green to using SQLCMD.
Desired output
:setvar dbNotServer @@servername
SELECT '$(dbNotServer)' AS workedResults
worked
DEVA\DEV2Fruitless pursuits
The first BOL link showed promise, all I had to do was use the SQLCMDSERVER but to no avail. Run within the context of SSMS in SQLCMD mode, it will throw a fatal scripting error
-- A fatal scripting error occurred.
-- Variable SQLCMDSERVER is not defined.
SELECT '$(SQLCMDSERVER)' AS [FatalScriptingError]Tumbleweed update
2011-08-12
In an attempt to reduct my problem to the simplest form, based on the answers, I oversimplified my queries (my apologies). A portion of the query I used is below. The two reponders are correct in their answers th
Solution
Unless I'm missing something, doesn't this work, without the apostrophes?:
It returns SERVER\INSTANCE for me in SQLCMD mode through SSMS.
Edit 02/12/2018
After coming across my answer to another question, and reviewing, I can see the issue, I believe it boils down to this:
This outputs @@servername, and the desired output is the value of @@servername.
The only way I've seen to do this is in the accepted answer here
:setvar dbNotServer @@servername
SELECT $(dbNotServer) AS workedIt returns SERVER\INSTANCE for me in SQLCMD mode through SSMS.
Edit 02/12/2018
After coming across my answer to another question, and reviewing, I can see the issue, I believe it boils down to this:
:setvar dbNotServer @@servername
print '$(dbNotServer)'This outputs @@servername, and the desired output is the value of @@servername.
The only way I've seen to do this is in the accepted answer here
Code Snippets
:setvar dbNotServer @@servername
SELECT $(dbNotServer) AS worked:setvar dbNotServer @@servername
print '$(dbNotServer)'Context
StackExchange Database Administrators Q#3727, answer score: 3
Revisions (0)
No revisions yet.