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

    -S SERVERNAME
    -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

      copy-backups.cmd
      robocopy /MAXAGE:1 "%1" "%2" "*.bak"
      copy-winrar.cmd
      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

Examples:

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:

C:\BIN>ONEOFF.CMD ONEOFF.SQL ONEOFF.LOG

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:

SET NOCOUNT ON;
GO
SELECT
'-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()
SET
NOCOUNT OFF;
GO

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'.




Links:

SQLCMD standalone installer(Microsoft SQL Server Native Client required for install, found on same page)
http://bit.ly/sqlcmd-and-sql-native-client

Using the sqlcmd Utility
http://technet.microsoft.com/en-us/library/ms180944.aspx

Command line reference
http://www.ss64.com/index.html

NotePad ++
http://notepad-plus.sourceforge.net