SQLCMD Mode gives you access to run command line executables from within SSMS and allows you to interact with those commands in your script. This can be quite powerful or just fun.

Example Use/Problem

Let’s say you need to run a long list of SQL scripts during a release or scheduled update. Rather than opening them all and running individually or creating one massive .sql file, I've come up with a way to programmatically make this happen.

Setup

First, you'll need to make sure SQLCMD mode is turned on for this Query window AND ALL FUTURE windows.

The sample files you need to run exist in the “C:\SQL_FilesToRun” folder and are attached below.

And these files contain

For starters, let’s make sure you understand some basic SQLCMD commands and rules.

• :SETVAR variableName "variableValue" - sets a variable named variableName to the value of variableValue

• !!<cmd> - two explanation points followed by <cmd>. Where <cmd> is ANY command line executable

• :stdout - used for “standard output” essentially means the current SSMS window

• :OUT - redirects the output of any following commands, both SQL and command line level to the output you define

• $(variableName) - to reference a previously set variable, you use this syntax

• :r - to run a specified file containing sql (which may also contain SQLCMD code)

For example, this code sets a variable and then uses it in a query:

The Process Explained

1. These variables need to be set to the directory where the .sql files exist, the name of a temporary file, and the final txt file that will actually be executed.

   

2. This line will clean up any file that might be left around. If you don’t do this and the file exists you’ll end up appending to it and running your files multiple times.

   

3. Now we’ll redirect any future output to our working file that we defined above.

   

4. Run a DIR command in bare format and ordered by filename.

   

The results of this are put into the working file defined in step 3. The reason to use the ordering is to help when the files are numbered or used with TFS task numbers to control that order. The result will give us a file containing this:

1. Here’s where the real magic happens. We’re using a DOS level FOR .. DO loop, usebackq and @ECHO to append the SQLCMD :r to the front of each filename. Here is the code for that:

This results in a file of SQLCMD run commands

We now set the output back to the SSMS window and turn off row counts

Now you simply call the :r command and pass the filename from step 5 above.

This results in the files being run in order

Alternatively, you can just open Notepad and display the file.

Conclusion

In its simplest implementation, you can use this method to run any number of files in a particular order without having to open and run them manually. If you integrate this into your SDLC you can simplify the deployment/upgrade process.

As with any of my articles/scripts, this is intended to enable you to take this and run. Customize as you see fit. The sample files are attached for your use.

Feel free to contact me here with any questions or comments.