Wednesday, July 02, 2014

Invoke-Sqlcmd - Where's my error?


So, I noticed some weird behavior when executing sql from powershell using the Invoke-Sqlcmd cmdlet.

I wanted to make sure that when I got an error, I updated the return value.

So, I modified my script file I was executing to simply :
 SELECT 1/0  

No problem, right? But everything worked fine.

So, I check the call to my script - made sure ErrorAction was set to stop, as suggested here

 $retValue = 0  
 try {  
      Invoke-Sqlcmd -Query "select 1/0;" -ServerInstance localhost -Database "tempdb" -Username "sa" -ErrorAction Stop -ErrorLevel 0 -SeverityLevel 0 | Out-Default  
 } catch {  
   #$_ | Out-Null  
      echo "Error!"  
      Write-Host($_)  
      $retValue = -1  
 }  
 return $retValue  

Results? What????
 PS SQLSERVER:\> C:\temp\test.ps1  
 0  

Now, its not like the error handling doesn't work. If I do anything successful before the divide-by-zero, it looks correct:
 $retValue = 0  
 try {  
      Invoke-Sqlcmd -Query "select 'anything successful'; select 1/0;" -ServerInstance localhost -Database "tempdb" -Username "sa" -ErrorAction Stop -ErrorLevel 0 -SeverityLevel 0 | Out-Default   
 } catch {  
   #$_ | Out-Null  
      echo "Error!"  
      Write-Host($_)  
      $retValue = -1  
 }  
 return $retValue  

Results... Ok...
 PS SQLSERVER:\> C:\temp\test.ps1  
 Column1                                                                                                         
 -------                                                                                                         
 anything successful                                                                                                   
 Error!  
 Divide by zero error encountered.  
 -1  

But whats going on? After a little research, it appears to be a BUG in the invoke-sqlcmd cmdlet. In fact, it seems they almost fixed it in CTP2 of SQL 2014, but for some reason chose to leave it out. The fix was a new parameter: "IncludeSqlUserErrors"
 invoke-sqlcmd -ServerInstance . -Database tempdb -Query 'select 1/0' -IncludeSqlUserErrors;  

But I can't find any documentation on this in the actual release. So, I'm guessing for whatever reason it was rolled back. Of course, I'm not sure why I should have to pass an additional parameter to get my error. In short, I don't think I can trust invoke-sqlcmd. I really like the syntax and its integration with powershell. But how will I ever feel confident that my script actually ran, with this sort of behavior?

So far, the divide by zero as the first statement in the batch is the only way I've found to replicate theis behavior.





No comments: