CODEWITHSUNDEEP
sqlsql-serversqlcmd
kevin
kevin

Reputation: 14065

escape character for $ in sqlcmd

I got this error Sqlcmd: Error: Syntax error at line 2 near command '1' when I run the sqlcmd for a script. When I check the script I found that 

INSERT INTO ABC.TableA(Name, Descr) VALUES ('Payment', N'$(162.66)')

So when I check online, some variables are like $(xxxx) for sqlcmd. How can I solve the issue? I'm using Microsoft SQL. Thank you.

Upvotes: 1

Views: 1453

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

There is no escape character, and yes, this is stupid. Either turn off variable substitution with the -x option if you're not using variables to begin with, or break up the expression so it no longer parses as a variable substitution:

INSERT INTO ABC.TableA(Name, Descr) VALUES ('Payment', N'$' + N'(162.66)')

Take care that the result is still of the correct type, where required.

In the rare case where even this is not possible (because a string literal is required and expressions are not permitted) you have to join them rather than beat them, and set a variable to the desired value:

:setvar descr $(1.2)
INSERT INTO ABC.TableA(Name, Descr) VALUES ('Payment', N'$(descr)')

Obviously this is rather headache inducing, so it's fortunate that a requirement for literals is rare. If you need to do this many times, you're better off with a variable just for $ itself:

:setvar dollar $
INSERT INTO ABC.TableA(Name, Descr) VALUES ('Payment', N'$(dollar)(1.2)')

Upvotes: 4

Related Questions