powershell try-catch doesn't capture invoke-sqlcmd error [duplicate]

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 that replaces it. That will not work if you're trying to use PowerShell from SQL Agent, however. — Bacon Bits
– Bacon Bits, Commented Apr 19, 2018 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
– Bacon Bits, Commented Apr 19, 2018 at 20:12
i'm an idiot and was copying an out dated version of my code... — DForck42
– DForck42, Commented Apr 19, 2018 at 20:16

A D · Accepted Answer · 2018-04-19 20:45:34Z

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)          
    }
}
}

DForck42 · Accepted Answer · 2018-04-19 20:15:21Z

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, 2018 at 20:15

DForck42

20.5k14 gold badges61 silver badges86 bronze badges

Collectives™ on Stack Overflow

powershell try-catch doesn't capture invoke-sqlcmd error [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related