Powershell Try Catch invoke-sqlcmd

Question

I'm having trouble trapping errors in PowerShell when SQL Server failed to connect using Invoke-Sqlcmd. This is the general code to demonstrate the problem:

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 specific instance error occurred while establishing a connection to SQL Server. The server was not found or was not available. Verify that the instance name is correct and that SQL Server is configured for a remote connection. (provider: Named Pipes provider, error: 40 - Could not open SQL Server connection)

I was expecting to get a single line statement: "Error connecting to Localhost \ fake server"

Answer 1

It seems that the error is considered non-terminal, which is a bit odd. Try Invoke-SqlCommand with an additional parameter: -ErrorAction Stop . If the error is not completed, this will turn it into a final error, which you can catch.

Answer 2

Posting additional information to complement @KeithHill's answer as an answer, as it is too long for a comment.

If an error record was created using the Write-Error cmdlet, it does not end and obeys the behavior specified by the -ErrorAction argument or the $ ErrorActionPreference system variable. Errors using throw terminate. See the documentation for Write-Error (note that PowerShell 4.0 and below do not have the -Exception parameter mentioned on the web page.) And about_Throw in the PowerShell help system.

If you want to add custom error information and make it the final 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 the termination to proceed as indicated by the -ErrorAction argument. In PowerShell 4.0 and below, the Write-Error cmdlet does not allow the -Exception argument and therefore will not throw an InnerException. This means that the caller will need 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 Write-Error -Message "Some additional error information." -Exception $_.Exception in its catch block.

Answer 3

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 result in an error and redirect it to zero and display your host entry