I have the following try-catch statement to process a DB table/dimension. For some reason, if i turn off the (MSSQLSERVER) service on the server, the 1st if statement to capture the exception that
Ensure that the server is running
is never touched and instead it goes to the second if condition and writes to host
Error Processing dimension: A connection cannot be made. Ensure that the server is running.
PS C:\> 'A connection cannot be made. Ensure that the server is running.' -like '*Ensure*'
True
Also, you are not checking the error properly in your try\catch. Using $_ under catch is the current exception (e.g. $_.Exception.Message). You can also look at catching a specific exception. Take a look at the free ebook on the left The Big Book of PowerShell Error Handling
PS C:\> ('A connection cannot be made. Ensure that the server is running.').contains('Ensure')
True
PS C:\> ('A connection cannot be made. Ensure that the server is running.').contains('Rob')
False
$error[0] is an object, not a string. As I mentioned earlier, you should be using $_ to represent the current exception. Also, you need to add -ErrorAction Stop to the command. If you want to test the message, it should be:
try {
Invoke-ProcessTable -DatabaseName "$DB" -Server "$server" -RefreshType "Full" -TableName "sometable" -ErrorAction Stop > $null
write-host "Processing completed!"
}
catch{
if ($_.Exception.Message.contains("Ensure that the server is running")) {
write-host "`r`nProcessing failed!`r`n(MSSQLSERVER) service is not running on $server!"
}
}
try
{
Invoke-ProcessTable -DatabaseName "$DB" -Server "$server" -RefreshType "Full" -TableName "sometable" > $null
write-host "Processing completed!"
}
catch [EnterTypeNameOfException]
{
if($Error[0].Exception.Message.Contains("Ensure that the server is running")) {
write-host "`r`nProcessing failed!`r`n(MSSQLSERVER) service is not running on $server!"
}
}