powershell try-catch doesn't capture invoke-sqlcmd error

Question

I have a function that I pass a server name to, then select some data from that server and insert it into a table on a centralized server. The list of servers is generated from a centralized server that isn't always up to date, and I would like for it to keep processing and log any errors that occur. However, my try-catch block doesn't seem to capture any errors.

I saw this question and tried that, and I still can't capture the errors. Here's a generic version of my function:

function Get-QueryData
{
    [CmdletBinding()]
        param 
        (
            [string] $server
        )

     $QuerySelect = "select * from dbo.Table"

    try
{
$results = Invoke-Sqlcmd -ServerInstance $server `
        -Database TheDatabase `
        -Query $QuerySelect `
        -QueryTimeout 20 `
        -OutputSqlErrors $True `
        -ConnectionTimeout 5 `
        -ErrorAction Stop
}
catch
{
    #to-do: log error
    Write Host "    An error occurred trying to get query info"
}
 #  remaining function inserts into central server, this part doesn't fail
 #  unless the first part fails
}

The error I get:

Invoke-Sqlcmd: A network-related or instance-specific error occurred while establishing a connection to SQL Server... CategoryInfo: InvalidOperations (:) [Invoke-SqlCmd] SqlException FullQualifiedErrorId : SqlExecutionError,Microsoft.SqlServer.Management.Powershell.GetScriptCommand

The reason I'm getting this error is that the server was stood up, and then had to be rebuilt, but it was already added to our central management server. I can't rely on this being cleaned up right away so I need the powershell to log the error and keep moving.

Output from $PsVersionTable.PSVersion

Major - 5

Minor - 1

Build - 14409

Revision - 1012

Appears to be a bug with some versions of the SQLPS module. I'd recommend trying with the newer [`SqlServer` PowerShell module](https://www.powershellgallery.com/packages/SqlServer/21.0.17240) that replaces it. That will not work if you're trying to use PowerShell from SQL Agent, however. — Bacon Bits, Apr 19 '18 at 20:11
If nothing works or you have to use the installed SQLPS module, then I'd fall back on the .Net SqlClient methods. — Bacon Bits, Apr 19 '18 at 20:12
i'm an idiot and was copying an out dated version of my code... — DForck42, Apr 19 '18 at 20:16

score 1 · Answer 1 · answered Apr 19 '18 at 20:45

In the Try{} set $flag=$True
In the Try{} remove the $results variable from the get-sqlcmd
In the Catch{} set $flag=$false
Add a finally{} that checks for the flag
In the Finally{} add an if(!x){y}else{z}
In the Finally{if(!x){y}else{z}}
if $flag is $false write to log and exit
if $flag is $true do your work and then write to log

I've provided [sting]$message >> $targetFile for simple logging. There are way better ways to do logging and I've used this example only as an simple example and not a suggestion.

function Get-QueryData 
{
[CmdletBinding()]
param(
    [string] $server
)
$QuerySelect = "select * from dbo.Table"
try
{
    $flag = $true
    Invoke-Sqlcmd -ServerInstance $server `
        -Database TheDatabase `
        -Query $QuerySelect `
        -QueryTimeout 20 `
        -OutputSqlErrors $True `
        -ConnectionTimeout 5 `
        -ErrorAction Stop
}
catch
{
    $flag = $false
    #to-do: log error with $_ which contains the error object
    Write-Host "An error occurred trying to get query info"
    $errorMessage = $_.Exception.Message
}
finaly
{
    # log errors if flag false
    if (!$flag){
        "[$(get-date)] - $errorMessage" >> `
            $(some target accessible path to a writable file)
        #Stop the function and return an exit code of 1
        Return 1            
    } else {
        #  remaining function inserts into central server
        "[$(get-date)] - Short Meaningful Success Message" >> `
            $(some target accessible path to a writable file)          
    }
}
}

score 0 · Accepted Answer · answered Apr 19 '18 at 20:15

0

So I'm an idiot and I was copying from an outdated copy. My NEW version that reflects the changes made per the link in my question works perfectly.

answered Apr 19 '18 at 20:15

DForck42

19,789
13
59
84

powershell try-catch doesn't capture invoke-sqlcmd error

2 Answers2