SQLCMD Syntax error when running script that runs fine in management studio
sql-server, sql-server-2008, sqlcmd
Solution
Yes, `$(id)` has special semantics in SQLCMD: it is a variable substitution. You can run commands like:
sqlcmd /E /S . /v variable=MyTable /Q "select * from $(variable)"
and this will select from MyTable. As you guess, the `/v` is the switch to define a variable. SSMS on the other hand does not, by default, interpret the SQL for variable substitution. SSMS can be made to do this, by checking the option 'Open query widows in SQLCMD mode'.
For mode details see Using sqlcmd with Scripting Variables.
Problem
When I run the this `select '$(''test'')'` in SQL Management Studio 2008 it returns `$('test')` When I run `sqlcmd -S SERVER -E -d DATABASE -q "select $(''test'')"` on the command line it returns `Sqlcmd: Error: Syntax error at line 1 near command '''.` If I remove the dollar sign it works. Is the "$" a special character? Is this a sqlcmd bug? How can I change the script to get the desired result.