Handling SQL Errors / Exceptions in PowerShell Script

Question

I am trying to catch the exceptions generated in the SQL query execution result in my log file using powershell script.

But however the code that I use below catches the exceptions in the syntax of my powershell script and not the SQL returned exceptions.

Also I would like to log the result set of my SQL script upon successful execution. How can I acheive this.

The following is my PowerShell script,

try
{

 if($Windows_authentication.tostring() -eq "1"){  
        invoke-SqlCmd -inputfile $Script_path\script.SQL    -serverinstance $Ser_name -database $Db_name -querytimeout 65535   }
        else
{
        invoke-SqlCmd -inputfile $Script_path\script.SQL   -serverinstance $Ser_name -database $Db_name -username $Userid -password $Passwd  -querytimeout 65535  }

        Write-Host "script.SQL Completed execution"
}       

Catch

{           
            $ErrorMsg = $_.Exception.Message
            Out-File -InputObject "The error message is - $ErrorMsg"  -Append -NoClobber -FilePath "$Script_path\log.txt"

            while( $ErrorMsg.InnerException ) { 
            $ErrorMsg = $ErrorMsg.InnerException
            write-output $ErrorMsg
            Out-File -InputObject "The error message is - $ErrorMsg" -Append -NoClobber -FilePath "$Script_path\log.txt"
         }

            break
}

Answer 1

Unless the exception being generated is caused on the Powershell / .Net side, the excepition handler doesn't process it. An example would be being unable to connect to the Sql Server. For any error in the TSQL code, error handling is done within Sql Server.

You can use try...catch in TSQL. There are more articles like Using TRY...CATCH in Transact-SQL and SO questions too.