The SQLCMD workbench

Robyn and Phil feel strongly that there are two striking features in SSMS that extends its usefulness dramatically. The first is the Template, and the second is the powerful functionality hidden by those strange words 'SQLCMD Mode'. Here they try to demonstrate how useful SQLCMD can be to anyone who is tasked with having to administer a database.

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:

Downloads

Tags: , , ,

  • 94664 views

  • Rate
    [Total: 0    Average: 0/5]
  • Dan Kennedy

    Slight problem
    Excellent article as always, but I just wanted to clarify I’m not doing something stupid. When I’m in SQLCMD mode in SSMS the following commands are not supported

    :ed
    :help
    :list
    :listvar
    :quit
    :serverlist

    Am I missing something ?

  • Robyn Page

    re: slight problem
    Several of the features of SQLCMD aren’t supported in SSMS, 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.

    I’m sorry we didn’t make this point in the article, but the article was getting a bit long and Phil was keen on demonstrating how to cope with output from sqlcmd

  • Dan Kennedy

    Slight problem: Updated
    Should learn to properly RTFM before posting silly questions. Sorry.

    As per BOL

    Query editor supports the following SQLCMD script keywords:

    [!!:]GO
    !! <command>
    :exit(statement)
    :Quit
    :r <filename>
    :setvar <var> <value>
    :connect server[instance] [-l login_timeout] [-U user [-P password]]
    :on error [ignore|exit]
    :error <filename>|stderr|stdout
    :out <filename>|stderr|stdout

  • Phil Factor

    re:Slight problem
    Yes, we’ll update the article slightly to make it clearer. One can get so engrossed in a subject as to occasionally miss out the obvious points.

    Normally, if you test stuff out in SSMS and then run it in Command-line SQLCMD, it should then work.

    What an oppertunity Microsoft lost by missing out any conditional processing like
    :if $addingStuff <> 0 then
    blah
    blah
    blah
    :endif

  • Peter Apostolakopoulos

    Slight ommission
    Very good article but I’m afraid the the declaration of the DataFile variable is missing in the 2nd example, namely:

    :setvar DataFile “Report.txt”

  • Phil Factor

    re: slight omission
    Thanks for spotting that.

    Heaven only knows where that walked to. It must have come out when we ‘depersonalied’ the test file. We’ll put that back in.

    I blame Robyn!

  • Anonymous

    :r with wildcard expansion?
    Is it possible to use :r with shell wildcards? For example:

    :r createDb-?.?.sql

    In my tests I couldn’t get this to work. Perhaps there’s a “set” option or command line switch to enable it?

  • Phil Factor

    Re: :r with wildcard expansion?
    It seems a good idea but I doubt whether Microsoft would have done it. How would you specify the order? Alphabetic, I suppose.

  • Eric Russell

    Executing very large scripts: SQLCMD vs. SSMS
    The other day, I had scripted out several large table inserts (using Red Gate’s SQL Data Compare) from the development system for deployment to QA.
    The problem I encountered was that many scripts were several hundred MBs in size, and SSMS would run out of memory when attempting to open them. Even if SSMS opened them, some of the scripts would fail during execution with a non-descript error (warning icon but no message in the Messages tab).
    However, SQLCMD.exe apparently doesn’t attempt to load the entire script into memory at once, and executed them just fine.

  • Anonymous

    only 1output file
    i have a script in which i run 20 scripts .. i had a different output file for every script i ran. But i had to open and look 20 files to see if there are any errors. I want all outputs to be in 1 output file. Is it possible? If i put the same output file name like in example.. it does not work.

    example:

    SQLCMD -S %Server% -d %Db% -U %User% -P %Pwd% -I -i “\mycompScripts

  • Robyn Page

    re: only 1 output file
    you can specify an output file separately, but the trouble you’ll find is that you are not allowed to append to an error file this way. You will need to send your errors to STDOUT and then redirect that output stream to ‘append to a file using the >> filename syntax. Then, all should be well.

    Personally, I’d stand the task on its head and do all the servers in the one script, using the method I describe in this article. The reason that Phil and I did this was to avoid precisely the problem you describe!

  • Nathan Church

    Modify batch variables.
    Can you modify the variables or have them interact? These samples don’t work but they give you an idea of what I am trying to do.

    example a:
    :setvar servname “betadb01”
    :setvar workpath “c:dbaout”
    :setvar outfile “out.txt”
    :out $(workpath)replace($(servname),”,’_’)$(outfile)
    :connect $(servname)

    example b:
    :setvar servname “betadb01”
    :setvar workpath “c:dbaout”
    :setvar outfile “out.txt”
    declare @outfile varchar(1000)
    Set @outfile = ‘$(workpath)$(servname)$(outfile)’
    Set @outfile = replace(@outfile, ”, ‘_’)
    :out @outfile
    :connect $(servname)

  • Ben Pittoors

    Line number incorrect in error SSMS feedback
    Yet another fantastic post Robyn! If you keep on doing this I’m going to have to start a Robyn Page fan-club for sure 😉

    Regarding SQLCMD from within SSMS I’d like to point out that, in addition to SSMS not supporting all SQLCMD features, it also screws up the line number information in any error output your script might produce by not counting the lines on which you issue or use SQLCMD commands and variables. Keep that in mind while trying to debug your work-in-progress scripts.

  • Glenn Jones

    Can you tell if a variable has been set?
    I’m trying to find a way to see if a variable has a value (like below) so that I can change the path of the script – does anyone have any suggestions?

    IF ($(User) = “”)
    BEGIN
    :connect $(Server) -E
    END
    ELSE
    :connect $(Server) -U $(User) -P $(Password)

  • Anonymous

    How to append data to an existing file?
    I am executing a sproc through SQL CMD Mode in management studio and i am logging the output messages of the sproc into a text file by using below cmd.
    !!sqlcmd -S TestServer -E -d TestDB-Q “exec xyz” -o C:Test.txt

    My question is if i wanted to append output to the existing file “Test.txt” everytime when i execute this cmd, then how can i do that?

  • Anonymous

    How to append data to an existing file?
    I am executing a sproc through SQL CMD Mode in management studio and i am logging the output messages of the sproc into a text file by using below cmd.
    !!sqlcmd -S TestServer -E -d TestDB-Q “exec xyz” -o C:Test.txt

    My question is if i wanted to append output to the existing file “Test.txt” everytime when i execute this cmd, then how can i do that?

  • Anonymous

    How to append data to an existing file?
    I am executing a sproc through SQL CMD Mode in management studio and i am logging the output messages of the sproc into a text file by using below cmd.
    !!sqlcmd -S TestServer -E -d TestDB-Q “exec xyz” -o C:Test.txt

    My question is if i wanted to append output to the existing file “Test.txt” everytime when i execute this cmd, then how can i do that?

  • Anonymous

    SQLCMD and hyphens in database names
    This is a VERY interesting article, I learned a lot from it. Judging by the list of articles available on the home page, I have a lot of interesting reading ahead. Thank you.

    That being said, there is a nuance of SQLCMD that I hope someone could help me with…the fact that database names with a hyphen used in a command are truncated by SQLCMD. Here is my example:

    I do a “select name from sysdatabases” command to get the list of existing databases, then copy copy that name to avoid typing mistakes for my script. In this case, the name is “SharePoint_Config_c9ab7476-2491-4db2-a782-2ad8bca244a3”

    I issue “USE SharePoint_Config_c9ab7476-2491-4db2-a782-2ad8bca244a3” and “go” commands to select my new database context.

    This is the message I get returned: “Could not locate entry in sysdatabases for database ‘SharePoint_Config_c9ab7476′”. (showing that the name is not read past the first “-” character).

    I’ve tried enclosing the database name in single and double quotes to no avail. Is there any way to tell SQLCMD to treat the hyphen as part of the string and not a command/meta-character?

    Thank you for whatever help you may be able to provide.

  • Anonymous

    SQLCMD and hyphens in database names
    You need to enclose the database name in brackets:

    USE [SharePoint_Config_c9ab7476-2491-4db2-a782-2ad8bca244a3]

    See the following pages:

    http://www.mydatabasesupport.com/forums/sqlserver-tools/243214-sql-2005-embedded-moving-sharepoint-services-database.html

    http://msdn2.microsoft.com/en-us/library/aa224033(SQL.80).aspx

  • Anonymous

    Maintaining database lists Issue?
    First off thanks for this article it has opened my eyes to a lot of possibilities.

    I was testing out a scenario using the Maintaining database lists example and it seems that the final running of the script takes place in the context second (last) connection listed.

    For example:
    Test.sql contains
    set nocount on
    select @@servername
    select @@version

    I ran the following in SSMS in SQLCMD Mode.

    :CONNECT Server01
    use master
    :r c:Test.sql

    :CONNECT Server02
    use master
    :r c:Test.sql

    Results:
    Server02
    Microsoft SQL Server 2005 Etc….

    Server02
    Microsoft SQL Server 2005 Etc….

    Is there something I am missing or do the scripts run under the last :CONNECT Server listed in the script?

  • jimkav@hotmail.com

    [ and her comrade ]
    Spent a few hours mucking about with some code that was hiccupping on a hyphenated database name. Imagine my joy when stumbling upon the glories of ‘[‘ and her comrade ‘]’.

  • idris

    Execute multiple .sql files
    Hi

    Im trying to figure out how i can execute multiple .sql files in a folder using sqlcmd. Do you have a solution for that ?

    Thanks

    • Phil Factor

      Re: Execute multiple .sql files

      The command…

      …can be executed in a batch file. The Batch file has the ability to repeatedly execute SQLCMD, passing each SQL command in turn. Remember to sort out an error file.

      • JayWizard

        Re: Execute multiple .sql files

        I have a T-SQL script in which I am using xp_cmdshell to execute SQLCMD multiple times.
        The xp_cmdshell is in a Cursor loop.

        Each time that SQLCMD is called it executes a certain T-SQL file (containing DML statements) located on one of the Server drives.

        If the DML in the executed T-SQL file ends with an Error, how can I pass the value of  that Error to the Main Calling T-SQL script ?

        A response would be highly appreciated !  If there are any better ways of doing this please let me know.

  • DanP

    Thanks a lot for the Article
    As regular each article from you
    is something worth to read.
    I very enjoyed to read this one.

    Thanks.
    http://madebysql.blogspot.com/

  • ronwarshawsky@yahoo.com

    sqlsplus/SQLS*Plus – SQL*Plus for SQL Server

    There is a free tool “SQLS*Plus” (on http://www.memfix.com ) which is like SQL*Plus for SQL Server.

    Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, HTML output, etc

  • sdmcnitt

    Trout is my favorite author… after Robyn and Phil of course.

    And so it goes.