Powershell Try Catch invoke-sqlcmd

powershell try-catch try-catch-finally
Eric Z picture Eric Z · Nov 28, 2012 · Viewed 10.2k times · Source

I am having problems catching an error in PowerShell when a connection fails to a SQL Server using Invoke-Sqlcmd. This is some generic code to demonstrate the issue:

CLS
$server = "Localhost\fake"
try
{
    Invoke-Sqlcmd -Query "SELECT DB_NAME() as [Database]" -Server $server
}
catch
{
    Write-Host "Error connecting to server " $server
}

I get the following error:

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I was expecting to get the one line statement: "Error connecting to server Localhost\fake"

Answer

Keith Hill picture Keith Hill · Nov 28, 2012

It would appear that error is considered non-terminating which is a bit odd. Try the Invoke-SqlCommand with an additional parameter: -ErrorAction Stop. If there error is non-terminating, this will convert it to a terminating error that you can catch.

Related questions

PowerShell try/catch/finally

I recently wrote a PowerShell script that works great - however, I'd like to now upgrade the script and add some error checking / handling - but I've been stumped at the first hurdle it seems. Why won't the following code …

Read More
powershell 2.0 try catch how to access the exception

This is the try catch in PowerShell 2.0 $urls = "http://www.google.com", "http://none.greenjump.nl", "http://www.nu.nl" $wc = New-Object System.Net.WebClient foreach($url in $urls) { try { $url $result=$wc.DownloadString($url) } catch [System.Net.WebException] { [void]$…

Read More
Powershell try/catch with test-connection

I'm trying to have offline computers recorded in a text file so that I can run them again at a later time. Doesn't seem that it is being recorded or caught in catch. function Get-ComputerNameChange { [CmdletBinding()] Param( [Parameter(Mandatory=$True,…

Read More