How do I change the SSDT SQLCMD variable in pre-script?
I'm trying to figure out how to change the SQLCMD variable on the fly and I couldn't get it to work.
The goal is to get the value from the SELECT and assign the SQLCMD variable to that value.
I tried:
1)
:servar myVariable
SELECT @myVariable = 1
2) Tried putting the file value with: OUT, but it says that:
Error 1 72006: Fatal script error: Command Out is not supported.
source to share
You need to declare a temporary sql @variable and assign a value from select to it.
Then initialize the sqlcmd variable with sql @variable.
DECLARE @sqlVar CHAR(1)
SELECT @sqlVar = '1'
:setvar myVar @sqlVar
SELECT $(myVar) as value
SELECT @sqlVar = '2'
:setvar myVar @sqlVar
SELECT $(myVar) as value
source to share
It's impossible. SQLCMD is just a pre-processor, before the script is even sent to the server.
The answer you accepted is somewhat confusing.
It doesn't actually assign the variable SQLCMD
on the fly in any meaningful way.
:setvar myVar @sqlVar
Just assigns the string "@sqlVar" to the SQL variable Cmd (redundant and wrong twice). This string is then used when replacing the string with $(myVar)
.
All of this happens before the script is even sent to the server (and obviously before execution starts, and the SQL variable is assigned any value)
The script result after replacing all $(myVar)
with @sqlVar
looks like this.
This is what is sent to the server.
DECLARE @sqlVar CHAR(1)
SELECT @sqlVar = '1'
SELECT @sqlVar as value
SELECT @sqlVar = '2'
SELECT @sqlVar as value
No on-the-fly assignment for SQLCMD variable. The only purpose is a SQL variable.
source to share
Answer at fooobar.com/questions/2221764 / ... :
Cool! Just be aware of the trap:
DECLARE @sqlVar CHAR(1)
SELECT @sqlVar = '1'
:setvar myVar @sqlVar
SELECT $(myVar) as value
GO
-- SELECT @sqlVar = '2'
-- :setvar myVar @sqlVar
SELECT $(myVar) as value
result:
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@sqlVar".
If we cannot fetch the value of the SQLCMD Variable over GO (when it was assigned to a regular variable), then there is no point in using the SQLCMD variable in the package.
Compare with the code that works:
:setvar myVar '1'
SELECT $(myVar) as value
GO
SELECT $(myVar) as value
And this magically works:
:OUT C:\Temp\SetVarTest.sql
declare @command varchar(100) = ':SETVAR myVar ' + cast((select count(*) from sys.objects) as varchar(10));
PRINT @command
GO
:OUT stdout
:r C:\Temp\SetVarTest.sql
GO
PRINT $(myVar)
More details in Hacking SQLCMD Mode .
source to share