(EDIT: For more info, consult THIS post, also written by me.)
A real quick one, but an important one. Invoke-Sqlcmd is great for many things, but one real weird behaviour is how it handles errors. See this script below:
(This assumes you are using PowerShell.SqlServer.Modules)
So, run the first and the command will fail, as it should (we are just trying to divide by zero.) An extra check here is that the LASTEXITCODE is FALSE, as we should expect.
However, run the second script and you will see NO error, and LASTEXITCODE as true. So what is happening here?
The keen-eyed amongst you will have recognised that the first Invoke-SqlCmd is using an extra parameter: -IncludeSqlUserErrors. Without this, errors won’t return.
This is really lousy behaviour: you’d expect user errors to return as an error by default, and to explicitly request this means that (I can imagine) that many people are running scripts out there and thinking that they are running correctly when that is clearly not the case.
And FWIW, this behaviour is also in the default “Invoke-Sqlcmd”