Issues with SQLCMD when using special characters

Problem

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.

Solution

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:

$(VARIABLE)

Let’s take a look at the following script:

 USE TestDB;
GO
CREATE TABLE BalanceSheetReport(
   BalanceSheetReportID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
   Item  VARCHAR(50),
   value VARCHAR(50),
);
INSERT INTO dbo.BalanceSheetReport (  Item, value )
VALUES ( 'Treasury Stock - Common', '$(500.45)' );
GO 

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.

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.

 USE TestDB;
GO
CREATE TABLE BalanceSheetReport(
   BalanceSheetReportID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
   Item  VARCHAR(50),
   value VARCHAR(50),
);
INSERT INTO dbo.BalanceSheetReport (  Item, value )
VALUES  ( 'Treasury Stock - Common', '$' + '(500.45)' );
GO 

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.

Next Steps

• Feel free to ask any questions you have on this tip in the Comments section below.
• If you are beginning to use sqlcmd or you never used it I suggest you read my previous tip as an introduction: Introduction to SQL Server’s sqlcmd utility.
• In case you need more information about executing script files with sqlcmd take a look at this tip: Execute SQL Server Script Files with the sqlcmd Utility.
• If you don’t know about sqlcmd variables, then this tip will help you: sqlcmd User Variables and Variable Precedence Examples.

Daniel Farina

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.

• MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019