http://www.sqlservercentral.com/blogs/chadmiller/2012/11/12/sqlcmd-is-dead-long-live-sqlcmd/

Printed 2014/07/30 07:30PM

Sqlcmd is dead. Long live Sqlcmd

By Chad Miller, 2012/11/12

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:

Unfortunately, doing all the above isn’t possible using invoke-sqlcmd, although it is with my invoke-sqlcmd2 function through extending functionality. The main sticking points with invoke-sqlcmd is that rows affected are not returned which some folks have gotten use to seeing in their sqlcmd.exe based scripts and getting at T-SQL PRINT and RAISERROR statement output is only available through the -verbose parameter which is difficult to redirect to a file.

SqlCmd Lives On

So, if I really want to get away from running sqlcmd.exe in favor of a Powershell cmdlet or function I’d have to write a lot code to duplicate all the output options, I’d have to test it and even then I’d have to provide fixes for things which inevitably happen in production as new issues are discovered. Just as developer sees the Base Class Library in .NET as code they don’t have to write I see native Windows console applications (or cmdlets) as portions of my script I don’t have to write or worry about working correctly.
sqlcmd.exe isn’t dead. The documentation doesn’t list sqlcmd as deprecated and there’s a nice table which shows the missing features in invoke-sqlcmd when compared to sqlcmd on the MSDN page. The documentation reinforces my own experience–invoke-sqlcmd lacks functionality in sqlcmd. Also the fact that sqlcmd isn’t marked for deprecation and has been enhanced (for example connecting to new SQL Server 2012 availability group listener) means I can continue to use it without concern it will go away in the next release or worry new functionality isn’t being added.

Invoke-SqlCmdExe

Since sqlcmd.exe is here to stay and solves issues with output format requirements I just needed to write a quick wrapper around sqlcmd.exe to do error handling/logging and ensure Powershell doesn’t get messed up on the parameters to sqlcmd.exe. I came up with a script I call Invoke-SqlCmdExe and posted on PoshCode.

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.