powershell:how to catch the error cause by invoke-sqlcmd?

Question

I want to catch the invoke-sql if there is anything wrong. But when I run the following command, and if the $sql is invalid, it can't be caught. How to catch this exception?

 try {
     Invoke-Sqlcmd -Query $sql -ServerInstance t1 -database db -QueryTimeout 65535 -ErrorAction 'Stop'
   } catch{
      "error when running sql $sql"
   }

Answer 1

I have no issue, using a script called test.ps1

add-pssnapin SqlServerCmdletSnapin100 get-host $sql = "selects * from syscomments" $server = "" $database = "" $username = "" $password = "" try {     Invoke-Sqlcmd -Query $sql -ServerInstance $server -database $database -QueryTimeout 65535 -ErrorAction 'Stop' -username $username -password $password } catch {   "error when running sql $sql"   Write-Host($error) } 

And the output

PS C:\> .\test.ps1 Name             : ConsoleHost Version          : 2.0 InstanceId       : 9ac019da-97bd-45d1-bfa5-65fb4d376dc6 UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface CurrentCulture   : en-AU CurrentUICulture : en-US PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy IsRunspacePushed : False Runspace         : System.Management.Automation.Runspaces.LocalRunspace  error when running sql selects * from syscomments  Incorrect syntax near '*'. 

what input parameters are you using, are you using powershell 1 or 2?