Search code examples
sqlsql-serversqlcmd

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.

Solution

  • 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)')