unable to catch non terminating errors in powershell when calling user defined function

Question

I have a user defined function which connects to a database and executes queries, it will be called in a loop to execute on multiple databases. If there is any error reported by the database server the script reports the error on the console & continues without terminating which is expected. However I would like to save that error to a variable & either write it to a file or save it to the database.

Below is the sample code: (its not complete ...)

function sqlCon($db, $sqlcmd)
{
    $connString = "Server='server_name';Database='$db';Integrated Security=sspi"
    $sqlConnect = New-Object System.Data.SqlClient.SqlConnection $connString
    $sqlConnect.Open()
    $sqlCommand = $sqlConnect.CreateCommand()
    $sqlCommand.CommandText = $sqlcmd
    $sqlCommand.ExecuteNonQuery() | Out-Null
    $sqlConnect.Close()      
    }
}

foreach($db in $databases){
   try{
    $sqlcmd = " use $($db);
          <<some sql query which updates or deletes>>"  
    sqlCon $db $sqlcmd
    #here i'm trying to catch non terminating errors but below statment never satisfies even 
    #i have errors while calling above function. Not sure if i'm missing anything here...
    if(-not($?)){
        throw "an error occured while executing query on $($db)."
    }   
       }
  catch{
    write-host "use this info to log into file or database." 
}
}

Note: I'm using powershell V2 but any options in powershell V3 also works for me.

Mathias R. Jessen · Accepted Answer · 2017-07-30 18:55:18Z

1

Add a CmdletBinding attribute to the sqlCon function and use the -ErrorVariable common parameter:

function sqlCon
{
    [CmdletBinding()]
    param([string]$db, [string]$sqlcmd)

    # ...      
}

foreach($db in $databases){
    sqlCon -db $db -sqlcmd $sqlcmd -ErrorVariable sqlErrors
    if($sqlErrors.Count -gt 0){
        # nonterminating errors were written, log items in $sqlErrors here 
    }
}

answered Jul 30, 2017 at 18:55

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

unable to catch non terminating errors in powershell when calling user defined function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related