SQL Server: Run A Script In Command Line Mode (SQLCMD) Inside SQL Server Management Studio (SSMS)

In mature IT shops, all deployment scripts in a release are run into the database using command-line batches as opposed to doing it by hand.

Error running SQLCMD scripts in SSMS

Occasionally, when one is testing a single script within a release to make sure it works, he/she needs to jump to the command line to test the script because commands like the following that are allowed in SQLCMD (the command-line SQL Server utility) will throw an error in SQL Server Management Studio (SSMS)

:Error C:\_dbLog\MyApp.err.log
:Out C:\_dbLog\MyApp.db.log
:On Error exit

Errors in SSMS:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘:’.
Msg 132, Level 15, State 1, Line 3
The label ‘C’ has already been declared. Label names must be unique within a query batch or stored procedure.

Isn’t it tedious to go to the command line just to quickly test a single script (not to mention the fact that sqlcmd is very verbose)? It is just as bad if we had to comment out the offending statements. We may forget to uncomment them after testing!

SQLCMD support within SSMS version 2008+

From SSMS for 2008 and forward sqlcmd is actually baked into SSMS. You can invoke sqlcmd command-line mode while staying within the comfort of SSMS.

To do so, in SSMS

  1. Select the query tab that has your script
  2. From the main menu  choose – “Query -> SQLCMD Mode”

This will run the script in the current tab in SQLCMD mode. All the command-line commands like the ones above will now be respected. Also, you are saved from the hassle of having to type-up a long command in the command-line.

For the true command-line junkie:

If you are a command-line junkie and refuse to use SSMS, here is the general syntax to run SQLCMD straightaway (simple example)

CD [To_DEPLOY_SCRIPTS_FOLDER]

c:\_dblog\MyApp>sqlcmd -S MyServer\MyInstance
       -i MasterScript.SQL
       -o C:\_dblog\MyApp.db.log

Where

  • MyServer\MyInstance is my SQL Server
  • MasterScript.sql is the script to run (may reference other scripts)
  • C:\_dblog\MyApp.db.log is the output file

Reference:

There are numerous options supported by SQLCMD. You can reference it here:

sqlcmd – http://technet.microsoft.com/en-us/library/ms162773.aspx

Advertisements

Leave a Reply

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