CODEWITHSUNDEEP
powershellpowershell-2.0powershell-3.0
S.D.
S.D.

Reputation: 1201

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

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.

Upvotes: 0

Views: 308

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174690

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

Upvotes: 1

Related Questions