Issues with SQLCMD when using special characters

By:   |   Comments (5)   |   Related: More > Scripts


You have a script file that runs smoothly in SQL Server Management Studio, but when you execute it using sqlcmd command line utility, that script doesn’t work. You keep getting error messages like “Sqlcmd: Error: Syntax error at line XX near command 'YY'” or “Sqlcmd: 'some command': Unexpected argument. Enter '-?' for help”. In this tip I will explain you how you can handle these kind of errors.


There are few things more frustrating to a programmer than when code was working great and then suddenly fails. Imagine that you are a T-SQL developer and you have created a script which is part of a solution, like an installation script or another kind of application or process. When testing the script with SSMS everything works great, but when the process runs using sqlcmd the script fails.  

Why does a script that runs smoothly in SSMS fail when executing in sqlcmd?

In order to be able to answer this question we must first understand the difference between SQL Server Management Studio and sqlcmd. Both tools are used to execute T-SQL scripts and Ad Hoc queries, but they are not built on the same technology. SQL Server Management Studio relies on the .NET framework SqlClient for execution in regular and SQLCMD mode in the Query Editor. On the other hand, sqlcmd uses an ODBC driver to achieve the same tasks.  There is a major difference between these methods and sqlcmd has a preprocessor that parses your script or ad hoc query and performs variable substitution and this is what can create failures. You can get more information about sqlcmd variables in my previous tip Using SQL Server sqlcmd scripting variables.

Special Characters in sqlcmd

Sqlcmd has special characters that when found in a script or ad hoc query may cause trouble. Every time you run a script or ad hoc query in sqlcmd, it's first processed by sqlcmd’s preprocessor to perform variable and command substitution. Remember that when writing an ad hoc query in sqlcmd console you can declare user variables by using the :setvar command followed by the variable name and its value for use. That is also true for script files. If you had the chance to read my previous tip Create SQL Server Disk Space Report for All Servers you have seen that I took advantage of the sqlcmd preprocessor to create a disk space report for all servers.

Since sqlcmd evaluates commands when they are at the beginning of a new line, it’s practically uncommon that you need to escape the ":" character. But that is not the case with the "$(" character combination which is used to reference sqlcmd variables. Remember that sqlcmd variables, either internal or user defined, must use the following notation where VARIABLE is the variable name:


Let’s take a look at the following script:


CREATE TABLE BalanceSheetReport(
   Item  VARCHAR(50),
   value VARCHAR(50),
INSERT INTO dbo.BalanceSheetReport (  Item, value )
VALUES ( 'Treasury Stock - Common', '$(500.45)' );

In the previous script I created a table named BalanceSheetReport that aims to resemble a financial report. It is not uncommon in finance to display the status of an account between parentheses in case that account has a negative balance. But the previous script when executed with sqlcmd will return the error message shown below.

Error - Description: This is what happens when you don

The reason behind this error is that sqlcmd interprets the value we are inserting $(500.45) as a variable, even when that is not a valid variable name.

In order to solve it we have two possibilities: either we split the conflicting text or we run the script by telling sqlcmd to disable variable substitution.

If your method of choice is splitting the text, which is the only way you can solve this error for Ad Hoc queries, then the previous script must be rewritten as follows.


CREATE TABLE BalanceSheetReport(
   Item  VARCHAR(50),
   value VARCHAR(50),

INSERT INTO dbo.BalanceSheetReport (  Item, value )
VALUES  ( 'Treasury Stock - Common', '$' + '(500.45)' );

Notice that I have split the ‘$(500.45)’ string into '$' + '(500.45)'. The idea is to leave the $ character apart from of the ( character. For example, if you instead split the string like '$(' + '500.45)' the script will also fail.

If you opt for the disabling the variable substitution method, you should add the –x parameter to the sqlcmd command line invocation and also include it as an argument in the script file you want to execute. Needless to say that this method only works when dealing with script files, it doesn’t work for Ad Hoc queries. Also, if you start sqlcmd with the –x parameter and use the :r sqlcmd command to load and execute a script file, the script execution will fail. In other words, disabling variable substitution only works on scripts loaded at sqlcmd command line invocation.

Something interesting to note is that the sqlcmd preprocessor distinguishes the comment marks, so if your script contains the symbol sequence $( in a comment, you don’t need to do anything in order to make your script run without any errors.

Interesting issue on sqlcmd with complex passwords

While I was writing this tip I found that when you try to log in into sqlcmd console using SQL Server authentication and the password of any given account contains the " character as the first and last character like for example "p", you won’t be able to log in with sqlcmd.

Sadly sqlcmd doesn’t have escape characters. Neither for scripts nor for the command line arguments. On the following screen capture you will see the experiment I did. Basically I created the user account buddy with password "pass" (the password includes the double quotes) and attempted to make a successful login attempt by trying different escape characters sequences, but none of these options worked.

Login Attempts - Description: On this screen capture you can see my unsuccessful login attempts.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 26, 2022 - 8:43:51 PM - Bill Neumann Back To Top (90419)
I can't seem to drop a domain user (domain\username) from SQL using SQLCMD. I've created a .sql file that contains a variable for the domain user to get around the "\" special character, and I can successfully drop several other non-domain users. I've also tried a version of the file that contained ALTER ROLE statements to remove each user from any roles. However, when that type of statement is executed I get the same darn error message as for the DROP USER statements.

I'm executing these SQLCMD statements within a .sh script on a Docker Container that runs Ubuntu, SQL, SQLCMD and other required utilities. Everything works fine with DBs that don't contain domain user accounts, like Adventure Works.

When I run SSMS against a local Windows-based SQL server I have no trouble using TSQL to delete the user. The reason I'm doing all this is that I'm trying to automate taking .bak files from cloud-based ERP systems and importing them into Azure SQL--where I need to use a .bakpac file.

:setvar RPUser [domain\username]

Those commands execute successfully but the output file says:
Msg 15151, Level 16, State 1, Server 3290f11e11cb, Line 5
Cannot drop the user 'domain/username', because it does not exist or you do not have permission.

Thanks in advance for any help or tips!
Bill Neumann

Tuesday, December 31, 2019 - 5:46:34 PM - Randolph West Back To Top (83570)

Thank you for helping me pin down a problem we're having along similar lines. Turns out doubling up the quotation mark at the beginning of the password works beautifully (thanks Paul!).

If you want to run a query, you have to put the credentials (both -U and -P) at the end, after the -Q parameter, to get the parser to understand the credentials and execute the query.

Wednesday, July 10, 2019 - 10:38:55 AM - Enmanuel Back To Top (81724)

Thank you, I was trying to insert some JQuery code into a database and was having this problem. This solved it!

Thursday, March 14, 2019 - 12:05:58 PM - François Breton Back To Top (79295)

 I don't know if anyone think of it, but could be a good idea to manage this issue (split $ and "(" ) when using MSSMS to generate script !

Monday, February 19, 2018 - 10:04:42 AM - Paul Back To Top (75244)

I too have been struggling with escaping special characters within SQLCMD. In your last example with the password, I found that this works:

SQLCMD -U buddy -P ""pass"

Thanks for the great article!

get free sql tips
agree to terms