Capturing in PowerShell different sqlcmd exitcode for connectivity/data issues

Question

I am calling sqlcmd from PowerShell to execute a T-SQL script. Currently I am using ":On Error exit" to exit the script if there is an error caused by the data used violating a constraint etc. This is handled by PowerShell detecting the $SqlcmdProcess.ExitCode of 1.

However, if there is a connectivity issue with the database, sqlcmd also gives an ExitCode of 1. Is there a way to set the :On Error ExitCode to something other than 1? I'm aware of using something like :Exit(SELECT 2) to do this, but I'd rather still use :On Error so I don't have to rewrite the script.

Chad Miller · Accepted Answer · 2012-07-25 17:22:37Z

0

You could use the exit keyword in Powershell. Here's an example

Create a script called sqlcmdexit.ps1, with something like the following:

$result = sqlcmd -S"missing" -d master -Q "select @@servername"
if ($result[1] -like "*Error Locating Server/Instance Specified*" -or $result[1] -like "*Could not open a connection to SQL Server*") {
    exit 99
}

Call script and observe exist code:

C:\Users\Public\bin>.\sqlcmdExit.ps1
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while
 establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

C:\Users\Public\bin>$LASTEXITCODE
99

I'm not aware of any way to set default ExitCode. Using start-process you could something similar:

$tempFile = [io.path]::GetTempFileName()
$exitCode = (Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
-S"missing" -d master -Q "select @@servername"
"@ -Wait -NoNewWindow -RedirectStandardOutput $tempFile -Passthru).ExitCode

if ($exitCode -eq 1) {
    $result = get-content $tempfile
    if ($result[1] -like "*Error Locating Server/Instance Specified*" -or $result[1] -like "*Could not open a connection to SQL Server*") {
        remove-item $tempFile
        Exit 99
    }
}
else {
    remove-item $tempfile
    Exit $exitCode
}

edited Jul 25, 2012 at 17:22

answered Jul 25, 2012 at 15:15

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1551288 Over a year ago

Thanks, this is very helpful and would allow me to distinguish the different types of error. However, capturing the result of sqlcmd in a variable would require a bit of rewriting as I'm currently calling sqlcmd by Start-Process (sorry, should have mentioned this). Do you know if there would be a way to do a similar thing with the process object. As far as I'm aware it only has ExitCode and ExitTime properties. Alternatively, and a more likely possibility, is there a way of setting the default ExitCode on error in the T-SQL?

user1551288 Over a year ago

Thanks! Not sure I'll do it exactly like that, but I think you've given me everything I need. Cheers.

Collectives™ on Stack Overflow

Capturing in PowerShell different sqlcmd exitcode for connectivity/data issues

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related