Sqlcmd is dead. Long live Sqlcmd

SQL Server 2008 and higher ship with the invoke-sqlcmd cmdlet while SQL Server 2005 and higher includes the sqlcmd.exe utility. Not wanting to take a dependency on invoke-sqlcmd, a few years ago I’d written a simpler variation I called invoke-sqlcmd2 which I’ve updated along with others.

You might think with later releases of SQL Server and the ability to create your own Powershell function that the old sqlcmd.exe utility isn’t needed, but you’d be wrong. There are still certain cases where using sqlcmd.exe is a better choice than invoke-sqlcmd or your own Powershell functions.

I reached this conclusion when converting some automated scripts from VBScript to Powershell used within our job scheduler. What I originally came up with was a simple variation of invoke-sqlcmd2 with some logging and error handling. I called this script invoke-sql.ps1 as posted on PoshCode. The script worked reasonably well for many scheduled jobs, but as I started looking at other existing VBScripts which used sqlcmd they relied on sqlcmd input and output options.

At first I thought I could reproduce the same output options as sqlcmd.exe including:

• Output rows affected
• Write T-SQL PRINT and RAISERROR statments to an output file
• The delimited output should not include header rows as export-csv does
• The delimited output shouldn’t quote all fields as export-csv does

SqlCmd Lives On

Invoke-SqlCmdExe

Invoke-SqlCmdExe Explained

At the top of the script I create a new Eventlog source. If it already exists the command will fail, so I’ll suppress and clear the error then move on. As stated in the script you must run the script as administrator in order to create a new Eventlog source on a server with UAC enabled, but once the Eventlog source is created you do not need to run as administrator. This is one of the many, many annoyances of UAC running on a server. You could run just this section of code to register a new Eventlog source.

1
2
3

#This must be run as administrator on Windows 2008 and higher!
New-EventLog -LogName Application -Source $Application -EA SilentlyContinue
$Error.Clear()

When running scheduled tasks, my preference is to use the Eventlog for logging messages. I’ll either setup a special Eventlog or use the Application log. The nice thing about using the Eventlog is that it’s always there, so you don’t have worry about messages not getting logged like you would if you were using a single remote database. Also a lot of monitoring tools like System Center or scheduling products include Eventlog  watchers, so you can build actions to respond to messages written to the Eventlog if needed. As part of using the Eventlog log for schedule task messages, you may want to create unique categories so your Eventlog watcher can look for particular events. The hashtable at the top of the script defines my unique categories:

1

$events = @{"ApplicationStartEvent" = "31101"; "ApplicationStopEvent" = "31104"; "DatabaseException" = "31725"; "ConfigurationException" = "31705";"BadDataException" = "31760"}

Powershell tends to get a little confused when calling certain native console applications with complex parameters. To avoid this issue with sqlcmd.exe I’ll wrap the call using start-process and here strings. I’ll also grab the exitcode and send all output to a file:

1
2
3

$exitCode = (Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
$Options
"@ -Wait -NoNewWindow -RedirectStandardOutput $tempFile -Passthru).ExitCode

If you use native console applications in Powershell you need to check exit codes as I’ve done in the script. If the error isn’t zero I’ll throw an error:

1
2
3
4
5
6
7

if ($ExitCode -eq 0) {
$msg = "ApplicationStopEvent"
Write-Message -Severity Information -Category $events.ApplicationStopEvent -Eventid 99 -ShortMessage $msg -Context $Context
}
else {
throw
}

The catch and finally statement blocks will handle writing error messages based on what was written to the output files and clean up any temp files.

Which Method to Use?

I haven’t given up on invoke-sqlcmd/invoke-sqlcmd2, but what I have done is come with a general rule on when to use one or the other. If I need to run a Powershell command and send the data to SQL or if I need to run a SQL command and send the data to Powershell I’ll use invoke-sqlcmd or some variation thereof. If just need to run a SQL query or produce an output file from a SQL query without a need to use the data in Powershell I’ll use sqlcmd.exe. You think of it like this my sqlcmd.exe based scripts are used for one-way operations, run a query which changes data or produces a file.