Notes From The Field: Using Invoke-Sqlcmd

Lately I’ve been working quite a bit with Invoke-Sqlcmd and there’s a few issues with how it handles errors that I feel make it a poor choice of tool to connect and execute SQL. Let’s take a look at a script that will return an expected error:

Funnily enough, this does not return an error. The “$?” is LASTEXITCODE, which means that as it returned as “True” (ie no issues) so PowerShell considers the query to be a success. This is a real problem. Even if we add error handling to the script, we still see the same result.

So, to get errors returned from using Invoke-SqlCmd, we have to use a special switch: –IncludeSqlUserErrors. This undocumented switch will do us the favour of returning the errors:

it’s worth noting that not all errors rely on this switch being included: duplicate values need no such switch to exist.

So, all this oddness aside, we’re home and dry if we use the switch, right? Not so fast…. see, it turns out that by using this switch, we now need to output something to the console every time. If we take our “divide by zero” example and divide by 1, we will get an output to the console and all will be right with the world.

However, let’s say we wanted to just create a table, how will Invoke-Sqlcmd handle this request?

Because we’re not outputting anything from the console, theInvoke-Sqlcmd fails with “Object reference…”. Run it without the switch, and it runs fine. Even a PRINT statement is not enough. All this basically means that Invoke-Sqlcmd sucks at dealing with errors and makes it pretty much unusable. There’s a Connect Issue raised some years back, but with no feedback. I think this is a pretty critical failiing and should be fixed ASAP, so please get voting.