Contents

1. Introduction
2. Adding a user or group
3. Running scripts in more than one database
4. The macro statements
5. Maintaining database lists
6. Gathering data
7. Command-line SQLCMD
8. Winding up, and further reading

Introduction

There are two ways of using SSMS to automate repetitive tasks that involve TSQL. One is by the use of templates. This gives you a ready-made library of standard routines that you can turn into executable TSQL simply by hitting Ctrl Shift M on the keyboard. Great for ad-hoc admin work, but not so hot for the routine stuff.

The other system uses macro-substitution at Runtime and involves SQLCMD mode. We’ll call them Macro Scripts, to distinguish them from templates.

Why bother with SQLCMD? If you are doing repetitive SQL Statements with just a change in one or two parameters then it is wonderful. If you are doing them to a whole lot of databases, maybe on different servers, it is wonderful. If you want it to be done in series, with error-checking and so on, or if you want it scheduled as a regular routine, then it is essential. It is also great to build and test a script in SSMS, and then run it as a command-line script, maybe put it on the windows scheduler. It gives you a lot of freedom to do admin tasks the way that suits your particular workload.

Adding a user or a group

Imagine you have to create a server login for a user and then grant him access to the current database: You open up a query window in SSMS and check that you are in SQLCMD mode (Query –> SQLCMD mode). If the lines beginning with a colon are shaded silver, then you are in SQLCMD mode

Here is a sample Macro Script to add a windows user or group to your database in the role you specify. You would need to change the parameters to suit, of course.

You’ll see immediately that adding the login, the name of the user and the role to the macro definition on the line that starts :setvar will speed things up from a simple script, if you have to add several users. But that is just a start.

Running scripts in more than one database

Imagine we have to add these database users on three databases on the same server. All we need to do is to

1. Save the code above, between --1 and --2, (so as to leave out the macros for the details of the user to add) to a file called AddUser.SQL
2. Open up a new query window in SSMS and check that you are in SQLCMD mode (Query –> SQLCMD mode). Run the following code in this query window:

Hmm, nice.

You’ll see a report of what you did and its success/failure in a file called Report.txt, and any errors in Errors.txt. you can even type them out from the script at the end using

Now save the following snippet to a file called removeuser.sql

Now execute the script again and you will have removed the user you just put in, just to tidy up.

the macro statements

You’ll see in the script above a number of keywords which start with a colon and must start at the beginning of the line these do some fairly magically useful things. Several of the features of SQLCMD aren’t supported in SSMS in ‘SQLCMD mode’, for various reasons. Sadly, the XML output is one of those too. Microsoft argue that these commands are relevant only to the command line version but I can’t see why they couldn’t have put them in just to make the correspondence between the two perfect.

:!! [<command>]

-Executes a command in the Windows command shell.

:connect server[\instance] [-l timeout] [-U user [-P password]]

-Connects to a SQL Server instance.

:ed

-Edits the current or last executed statement cache.

:error <dest>

-Redirects error output to a file, stderr, or stdout.

:exit

-Quits sqlcmd immediately.

:exit()

-Execute statement cache; quit with no return value.

:exit(<query>)

-Execute the specified query; returns numeric result.

go [<n>]

-Executes the statement cache (n times).

:help

-Shows this list of commands.

:list

-Prints the content of the statement cache.

:listvar

-Lists the set sqlcmd scripting variables.

:on error [exit|ignore]

-Action for batch or sqlcmd command errors.

:out <filename>|stderr|stdout

-Redirects query output to a file, stderr, or stdout.

:perftrace <filename>|stderr|stdout

-Redirects timing output to a file, stderr, or stdout.

:quit

-Quits sqlcmd immediately.

:r <filename>

-Append file contents to the statement cache.

:reset

-Discards the statement cache.

:serverlist

-Lists local and SQL Servers on the network.

:setvar {variable}

-Removes a sqlcmd scripting variable.

:setvar <variable> <value>

-Sets a sqlcmd scripting variable.

Some of these commands are extremely useful, others will make you scratch your head in puzzlement. the ones that work in ‘SQLCMD mode’ within SSMS are:

You can run these scripts from the command line, specifying, for example, the server you want to run it on. (if you have to use a SQL Server login, you can specify userid and password).

now, as it stands, this is OK but not entirely useful for us, but you can also define variables for the command line version using the -v . You have to enclose each value in quotation marks if the value contains spaces.

So what you have here is a means of adding a user (or any other repetitive action) to any database on any server via a batch.

It is good but it aint good enough. Where, for example, is the error handling? (you cannot concatenate to the end of a file with SQLCMD)

Maintaining database lists

An alternative approach is to maintain a list of servers and databases for running maintenance tasks. Each domain role (e.g. Sales, Marketing, development, manufacturing) will require a different list of servers and databases. We want something which will specify the list, and the action to run, along with the parameters.

The list will look something like this:

We save this lot as MyDatabaseList.SQL (or whatever).

So now all you have to do is to set all your variables, and execute the file.

And you can see that it will do a whole list of users, though rather slowly! You can, for example, save this file without the user definitions, and call it repeatedly from a file that has a list containing the ‘defines’ each user followed by the R command for the file you’ve just saved.)

Gathering data

Having got this far, you’ll wonder how you can collect information from a whole lot of servers and databases, using SQLCMD. What is more, can one use the batch mode on a timer to collect information regularly?

Yes you can.

When you have a number of servers to look after, it usually pays to keep information about them in a central database. Any DBA will tell you that there are a number of database and server objects that have to be monitored, such as the disk space, event logs, backup history, transaction log size, tempDB size, and database users.

Once you have all this information to hand in a single database, it is easy to set up all sorts of simple monitoring and alerting systems which catch your eye and warn you of potential trouble before it happens.

We’ll use SQLCMD to set up, as an example of what we mean, a user database that lists all the users on all the databases on all your servers, along with their details . Not only does it then become easier to answer questions such as ‘Which databases on which server does x have access to?’, but, if you do regular updates, you can tell when users were added or removed.

For a lot of operations, this sort of work is best done in SMO/DMO, but nothing beats SQLCMD for getting something up and running quickly. Once you have a system in place and have a clearer idea of how valuable it is likely to be, then you can re-implement it as a more robust system using SMO/DMO

Lets imagine we want to keep a list of database users on a central database called MyServers, along with some general information about them such as their loginName and Groupname. We’ll keep this simple for the example …

First step is to write some SQL that will give us a resultset of all the users for a server and the databases they have access to. Users who have SQL 2000 servers will need to change Varchar(MAX) to Varchar(8000) for those servers.

OK So far! But I’ve got 80 servers! The first thing is to save the code above here to a file. We’ll call it GetUsers.SQL (We’ve put it in the SQL script available to download at the bottom of the article).

Now, assuming you’re using SSMS, click the Query->SQLCMD mode menu item.

Let’s set our ‘macro’ SQLCMD variables just so we can execute the batch in SSMS before making it a command-line thing. Then, the command line can specify these values.

We can then break down the file so that we need only have, and maintain one list of servers that we can then use to execute any number of script files.

Command-Line SQLCMD- The Command-line switches

The joy of SQLCMD is that you can develop scripts in SSMS and then execute tyhem in SQLCMD command-line. Actually, whenever Phil opens up command-line SQLCMD, he gives a little sigh of satisfaction at the stark character-based interface, free of any menus, popups, or other tat. (Phil: all entirely untrue, but it is a relief sometimes to develop stuff in SQLCMD command-line) SQLCMD is a direct replacement for isql and osql, but this is not the only justification for it.

Just for reference, here are the switches, so you don’t have to look it all up on MSDN!

-U	login id	-P	password
-S	server	-H	hostname
-E	trusted connection	-d	use database name
-l	login timeout	-t	query timeout
-h	headers	-s	colseparator
-w	screen width	-a	packetsize
-e	echo input	-I	Enable Quoted Identifiers
-c	cmdend	-L	list servers
-LC	list servers clean output	-q	“cmdline query”
-Q	“cmdline query” and exit	-m	errorlevel
-V	severitylevel	-W	remove trailing spaces
-u	unicode output	-r(0|1)	msgs to stderr
-i	inputfile	-o	outputfile
-z	new password	-f	<codepage> | i:<codepage>(,o:<codepage>)
-K1|2]	remove[replace] control characters]	-Z	new password and exit
-k(1|2)	remove(replace) control characters	-y	variable length type display width
-Y	fixed length type display width	-p(1)	print statistics (colon format)
-R	use client regional setting	-b	On error batch abort
-v	var = “value”…	-A	dedicated admin connection
-X(1)	disable commands, startup script, enviroment variables (and exit)	-x	disable variable substitution
-?	show syntax summary

Winding up, and further reading

So there you are, but it is really just a quick taste of the sort of automation you can achieve with the tools provided. What makes SQLCMD so good is the speed at which one can debug and run scripts. It is real rapid application development, and all without having to crank up C# or VB! We should strike a note of caution here. SQLCMD is not designed to be for everyone’s tastes. If you feel at all nervous about the complexity of the approach, it is much better to use something like SQL Multi Script which enables you to execute scripts in parallel from a simple GUI.

Have a look at:

• The SQLCMD utility
• sqlcmd Utility Tutorial
• Intoduction to SQLCMD
• Using sqlcmd with Scripting Variables