Basic command prompt script tools will be around for some time to come (and more I hope), so better get used to it. Many things can be accomplished using those simple “scripts runner”. It always been part of my tool box and many of my admin or troubleshooting scripts are automated using it.

Utility tools like ISQL, OSQL and ISQLW have all their cool features but none of them had the possibility to have variable values set on demand that would permit to initialize special variables declared into the script file to run dynamically. Those of you who worked with SQLPLUS or MYSQL utility tools will welcome the new SQL Server SQLCMD utility tool. This tool comes with all versions of next SQL Server 2005. You will find it under the “binn” folder once the product is installed, SQLEXPRESS or Server.

First of all, I can connect to any SQL Server versions with it. It has a rich set of options like it’s predecessor (isql, osql) and you can now also set or change the value of some of them from within the script you are running it with. Let’s say that one of the option you set when the utility was called was “–s” to set the column separator, you can now later in the script change that value by invoking the SQLCMDCOLSEP scripting variable. You do so by using the “:setvar”. You can also use the “:setvar” to declare and initialize special variables inside your script file.

You can set environment variables in the OS shell and invoke it to get its value using the special syntactic “$(VarName)”. Also, with that variable declaration form, you have the possibility to pass values for those declared variables at the utility call time.

To illustrate the use of this and the utility of it can have in a real life situation, I will use one of Microsoft’s PSS (Professional Service Support) procedure called sp_blocker_pss80. The documentation for the procedure used to monitor process blocking can be found at http://support.microsoft.com/default.aspx?scid=kb;en-us;271509#kb2

It is a good idea to read the document where this link will bring you to. Once you are comfortable with it you can run it in your trouble making production environments … This will give you only an aspect of what the problem might be if locking contention exists, you might need other troubleshooting tool logs (Profiles, System Monitor, …) as well to get a correct picture of the situation.

I have split Microsoft script in 2 procedure scripts for functionality purpose but the procedure is the same. (By the way, this procedure is 2000 compatible, will run on 2005 as is but with a lot of un-significant logging)

sp_blocker_pss80v15sp3.prc

sp_blocker_pss80v15.prc

Those are Microsoft procedures for more information read the link above.

I also added a script to verify if the user running the commands has sufficient privileges : CheckRights.sql

No need to explain anything about this one.

Next is the script to run the procedure in a loop: GetBlocker.sql

You will find inside that script SQLCMD special syntax to work with scripting variables $(TheVarName). Most of the variables you will see are used to set the time the procedure will run and at what interval, as well as a couple of input variables needed to set some of the blocker procedure options.

And finally the calling batch file: GetBlocker.bat

You will find in this file how to use the “–v” switch to initialize variables to use with SQLCMD. Hopefully you will also find some tricks to use in developing other dynamic tasks following this same approach.

You are now all equipped to start troubleshooting blocked processes and performance using SQL Server 2005 command prompt tool new functionalities.