SQL Server ':Setvar' Error

SQL Server ':setvar' Error

The :setvar only works in SQL command mode, so you are possibly within normal SQL execution in the management studio and have not swapped to command mode.

This can be done through the user interface in SQL Server Management Studio by going to the "Query" menu, and selecting "SQLCMD mode."

Conditionally set a SQLCMD variable in a scriptfile?

SQLCMD variable values are assigned by the tool before the batch is sent to SQL Server for execution. Consequently, it is not possible to set the value, conditionally or otherwise, using T-SQL (which runs on the server).

One workaround is to invoke SQLCMD twice, once to create the SETVAR commands using T-SQL and pipe it to a file on the client and then to execute the final script that include the file using the ':r' SQLCMD command. Example (which could be encapsulated in an OS command file):

sqlcmd -U "username" -S "ipaddr" -P "password" -I -Q"IF SERVERPROPERTY('MachineName') = 'X' PRINT ':setvar USER User1' ELSE PRINT ':setvar USER User1';" >"C:\Scripts\setvarcommands.sql"
sqlcmd -U "username" -S "ipaddr" -P "password" -I -i "C:\Scripts\scriptname.sql"

Contents of c:\Scripts\scriptname.sql:

:r "C:\Scripts\setvarcommands.sql"
PRINT '$(USER)';

There may be more elegant ways to create the setvarcommands.sql but this is the first that came to mind.

Note that I specified the -I SQLCMD parameter so that QUOTED_IDENTIFIERS ON is used for script execution. The default is OFF for backwards compatibility and may result in issues with some DDL or DML operations. I recommend -I' always be specified.

Getting an Error The file specified for :r command was not found

Remove the semi-colon for the :r commands. Semi-colon is used to terminate a SQL command. :r is a tool "command", processed by sqlcmd.exe/SSMS.

How to change SSDT SQLCMD variable in pre-script?

You need to declare a temporary sql @variable and assign it value from select.

Then initialize sqlcmd variable using 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

incorrect syntax near ':' in SQL

OK.. it was simple. Enabling the "SQLCMD Mode" from the SSMS menu solved the error. Thanks everyone for your replies.

I guess, there are tons of such "Incorrect syntax near 'x'" errors reported on stackoverflow. Guess, those will go away with this fix.

The file specified for :r command was not found

I just solved my own problem. I changed the single quotes around VERSIONS.sql to double quotes and it worked.

Why are they not interchangeable?

SQLCMD.exe exiting with syntax error where not syntax error exists

To summarize from my comments:

Verify the command line options you're using for sqlcmd.exe. Note that the options are case sensitive. You should be able to use -b -e to tell sqlcmd to stop executing when it encounters an error and to also echo the command that it's trying to run.

You should also consider the -x (disable variable substitution) and -X (disable script commands) parameters. If data you're inserting potentially contains data that looks like an sqlcmd substitution variable, $(variable), you will need to specify -x to disable that feature.

---

Related Topics