SQLCMD Syntax error when running script that runs fine in management studio

Question

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.

Answer 1

the command you are trying to run:

select $('test')

is not valid. As you note, when you remove the "$" it works:

select ('test')

I'm not sure what you are really trying to do, you have three " double quote characters, you could try using this command:

select '$(test)'

which would be:

sqlcmd -S SERVER -E -d DATABASE -q "select ''$(test)''"

Answer 2

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.