4

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.

Improve this question
2
  • I am still confused, with @KM, about why you’re sending SELECT $(''test'') to SQL Server from sqlcmd whereas you’re sending SELECT '$(''test'')' from SSMS. Commented Sep 16, 2013 at 19:38
  • I'm not sure why the 2 statements are different in that way. This was legacy code that was sending javascript from a table through a query. I needed to be able to create the database from scripts on the command line and was running into issues with sqlcmd variables. Commented Oct 1, 2013 at 12:23

2 Answers 2

Reset to default
15

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, thats what I need to know. The -x switch disables variables, so this works: sqlcmd -S SERVER -E -d DATABASE -x -q "select $(''test'')"
Thank you. I needed to know how to disable variables using the -x parameter. I was getting an error on a giant copy, and couldn't find the reason why until now.
@Brain2000 if you have questions, post a question, not a comment.
@RemusRusanu My question was the same as Troy's. I was thanking Troy for including how to disable variable substitution with the -x parameter, because that was the answer I needed.
0

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)''"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.