SQLCMD mode in SSMS – Final part in the series “Underappreciated features of Microsoft SQL Server”


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”

image

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:TempSqlCmdOutputtestoutput.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.

image

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.

Advertisements

4 thoughts on “SQLCMD mode in SSMS – Final part in the series “Underappreciated features of Microsoft SQL Server”

  1. abhimanyu

    Wow !

    I wished i would have known this initially when in my last project assignment we need to exchange results of sub-queries with client in excel sheets as requirement analysis.
    I did got the results in excel sheet…just faced a little challenge in it. The results were not well formatted in the sheet which discourage comfortable analysis of the result.

    Can we get a proper formatted result in excel with some alteration in the way you suggested above?

    Like

    Reply
  2. balakrishna141

    HI nakul,

    I am very new to sqlcmd , just am trying to connect my server BUT i could’t able.

    If you send me the steps it would be great help.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s