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"

Answer 1

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.