Powershell Try Catch invoke-sqlcmd

Question

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"

I'm on powershell v3 and your code give me your custom error. — CB., Nov 28 '12 at 17:14

score 21 · Accepted Answer · answered Nov 28 '12 at 17:13

21

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.

answered Nov 28 '12 at 17:13

Keith Hill

194,368
42
353
369

There is a downside to this - by adding `-ErrorAction Stop` you have Powershell stop on the first line of the error, which means you lose critical information being written to the error output about where exactly your error failed. Basically, Powershell's error handling is hot garbage. But that's not news. – Pxtl Jul 03 '19 at 16:24
1

Well, you don't **lose** that info. If the command with `-ErrorAction Stop` wasn't inside a try/catch that info would still get written to the error stream. And if was still in a `try/catch` you can write the error info from inside the `catch`: `catch { Write-Error $_ }`. – Keith Hill Jul 04 '19 at 17:20

JamieSee · Answer 2 · 2017-06-12T21:10:04.237

Posting additional info to supplement the answer by @KeithHill as an answer since it is too long for a comment.

If the error record was created by the Write-Error commandlet it is non-terminating and subject to the behavior specified by the -ErrorAction argument or the $ErrorActionPreference system variable. Errors using throw are terminating. See the documentation for Write-Error (Note that PowerShell 4.0 and below do not have the -Exception parameter mentioned in the web page.) and about_Throw in the PowerShell help system.

If you want to add custom error information and make it a terminating error, throw from your catch block as follows:

catch
{
    throw (New-Object System.Exception "Error connecting to server $($server).", $_.Exception)
}

You can use Write-Error if you want termination to behave as specified by the -ErrorAction argument. In PowerShell 4.0 and below the Write-Error commandlet does not allow a -Exception argument and therefore will not provide an InnerException. That means that the caller would have to examine the collection in the $Error system variable if it needs to determine the original exception. In later versions you can Use Write-Error -Message "Some additional error information." -Exception $_.Exception in your catch block.

score -2 · Answer 3 · edited Nov 28 '12 at 19:11

-2

Try

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

This will capture the error and redirect it to null and display your write-host

edited Nov 28 '12 at 19:11

CB.

58,865
9
159
159

answered Nov 28 '12 at 17:17

user983965

1,111
3
17
31

I think you have an extra } at the end of the second to last line. I tried this and I still got the error. – Eric Z Nov 28 '12 at 17:29

Powershell Try Catch invoke-sqlcmd

3 Answers3

Linked

Related