Friday, February 20, 2009

Automation Using SQLCMD with Batch

In the January 2009 Meeting of the Utah County SQL Server Users Group I gave a presentation on using SQLCMD.EXE with batch files for automating T-SQL scripts.

Automation Using SQLCMD with batch

  1. Why SQLCMD?

    1. Repetitive processes, same targets, deployment tasks

      1. Scenarios

        1. Run schema change on multiple server/db targets

        2. CREATE/ALTER stored proc on multiple server/db targets

        3. Query Permissions on multiple server/db targets

        4. Speed! Can be much faster than GUI

  2. SQLCMD basics

    install location:
    C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

    -d DBname
    -i inputfile
    >> append operator

  3. CMD Batch Overview (2K, XP, 2003, Vista, Server 2008)

    REM I love comments!
    :: Protect yourself at the command line with this!

    %1 first argument passed
    %2 second argument passed

    1. Arguments examples – copying files to a server

      robocopy /MAXAGE:1 "%1" "%2" "*.bak"
      robocopy /S "%programfiles%\WinRAR" "\\%1\c$\Program Files\WinRAR" *.exe

    2. use self-documenting batch files - commented examples or output when no arguments are passed

      IF "%~1"=="" goto help

  4. Putting it all together


Demonstrate sqlcmd to multiple targets, then parse command line by copying everything before the append operator.

The batch file, ONEOFF.CMD contains the targets for the T-SQL to be executed.
ONEOFF.CMD accepts two arguments: the name of the .SQL file containing the T-SQL commands, and a log file for output.

I saved all 3 files to the same directory( C:\BIN ) to simplify the example and ran the following:


The log file can be very helpful in determining which servers/databases successfully parsed the commands, and which targets failed. The first part of the .SQL file contains the following 5 lines:

'-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()

These 5 lines above format the outputted log file with the server name, database name, and timestamp. In the example log file output below, the "server" name is my laptop(T60-LAPTOP) since I ran the demo locally. The first 3 lines show that database Chile accepted the .SQL without errors. Lines 4-7 show errors when running against database Cuba.
------------------- -------------------- -----------------------
-- T60-LAPTOP Chile 2009-01-29 18:43:22.793

------------------- -------------------- -----------------------
-- T60-LAPTOP Cuba 2009-01-29 18:50:13.543
Msg 156, Level 15, State 1, Server T60-LAPTOP, Procedure sp_BackupNow, Line 7
Incorrect syntax near the keyword 'BEGIN'.


SQLCMD standalone installer(Microsoft SQL Server Native Client required for install, found on same page)

Using the sqlcmd Utility

Command line reference

NotePad ++

No comments: