Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren's editorial on SQLServerCentral.com.

The pre-requisite for today’s session is a basic knowledge of connecting to a SQL Server using the command line.

SQLCMD Utility

Batch files and command line utilities are perhaps some of the most underappreciated features of any IT establishment. When connecting to a database, we had the “OSQL” utility in the days of SQL Server 2000.

When SQL Server 2005 came about, it introduced the SQLCMD utility. This utility is a replacement of OSQL, and has a large number of advantages over the same. To list a few:

1. Parameterized variable support
2. Use SQLCMD to execute both T-SQL and DOS-commands (Refer Pinal’s post here)
3. Improved Performance & security
4. Support for DAC
5. Multi-server connection

There may be more features of SQLCMD, which you are more than welcome to mention in the discussion associated with this post.

SQLCMD rules

There are some basic rules for any SQLCMD query. They can be summarized as under:

1. SQLCMD commands must be the first statement on a line
2. Only one SQLCMD command permitted on each line
3. SQLCMD commands can be preceded by white spaces or comments
4. Before the start of each SQLCMD command, use a colon (“:”). Only exception to this rule are “exit” and “!!” commands
5. Single-line comment characters are two hyphens (--) and must appear at the beginning of a line

SQLCMD and SSMS

What many people don’t know is that the SQLCMD mode is also accessible from the SQL Server Management Studio. This gives you as a developer, the comfort of writing and executing SQLCMD files using the familiar environment of the SQL Server Management Studio.

Enabling SQLCMD mode in the SSMS

1. Launch the SQL server Management Studio
2. Click on “New Query” to open a new instance of the T-SQL Query editor
3. Go to Query –> “SQLCMD”

Writing a simple query in the query editor

Taking an example from the Books On Line (http://msdn.microsoft.com/en-us/library/aa833281.aspx), write the following code in the T-SQL editor:

!!mkdir c:\TempSqlcmdOutput
:out c:\TempSqlCmdOutput\testoutput.txt
select @@VERSION as 'Server Version'
!!dir
GO
select @@SERVERNAME as 'Server Name'
GO 4

Now, click “Execute” or hit “F5” to execute the query. Navigate out to the directory - “C:\TempSqlCmdOutput”, and open the “testoutput.txt” file. You can see that the combined output of the DOS-commands and the T-SQL Query is available there.

Execution technology

When run from the command-line SQLCMD uses the OLE DB provider, whereas when run from SSMS, it uses the Microsoft .NET framework sqlclient.

Some limitations of the SQLCMD mode in SSMS

Because the SSMS cannot display interactive prompts, such queries cannot be executed from within the SSMS, and must be used from the command line.

Conclusion

We can now see how the most modern SSMS interface can help us write efficient command line SQLCMD queries. I personally use the SSMS to edit production level SQLCMD queries for our product and hence I urge you all to experiment with the SQLCMD mode and experience the raw power that it has to offer.

This post concludes my series on the "Under Appreciated Features of SQL Server". It has been great learning new stuff about SQL Server throughout writing of this series. I will be sharing a few of the nuggets that I have accumulated in the time to come. I hope that you, my kind readers will enjoy my future posts as well.

Until we meet next time,

Be courteous. Drive responsibly.