Error detection from Powershell Invoke-Sqlcmd not always working?

Question

The database is updated by executing a list of queries that are located in a folder.

I need to be able to detect any errors that would also result in "Query completed with errors" in SQL Server Management Studio.

The following works to detect the "Invalid Object" error:

PS SQLSERVER:\> $ErrorActionPreference
Stop
PS SQLSERVER:\> $Error.Clear()
PS SQLSERVER:\> $Error
PS SQLSERVER:\> Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Query "select * from doesnotexist" -ErrorAction SilentlyContinue
PS SQLSERVER:\> $Error.Exception
Invalid object name 'doesnotexist'.
PS SQLSERVER:\>

Doing the same for select 1/0 does not work:

PS SQLSERVER:\> $ErrorActionPreference
Stop
PS SQLSERVER:\> $Error.Clear()
PS SQLSERVER:\> $Error
PS SQLSERVER:\> Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Query "select 1/0" -ErrorAction SilentlyContinue
PS SQLSERVER:\> $Error.Exception
PS SQLSERVER:\>

I would expect this to result in a "Divide by zero error encountered" error just like in SSMS.

Not detecting this particular error makes me wonder if other errors will also remain undetected.

Any idea why this is a happening and how I can make sure the all errors will be detected?

UPDATE

It turns out that I do not have Invoke-Sqlcmd available on the server I am installing, so on second thought I have to use sqlcmd.exe.

I think this is working for me:

$tempfile = [io.path]::GetTempFileName()
$cmd = [string]::Format("sqlcmd -S {0} -U {1} -P {2} -d {3} -i {4} -b > $tempfile",
    $g_connectionstring."Data Source",
    $g_connectionstring."User ID",
    $g_connectionstring."Password",
    $g_connectionstring."Initial Catalog",
    $path)
Invoke-Expression -Command $cmd
if ($LASTEXITCODE)
{
    $err = Get-Content $tempfile | Out-String
    Corax-Message "SQL" "Error" $err
    exit
}
Remove-Item $tempfile

Answer 1

Regardless of the ErrorAction setting, invoke-sqlcmd cmdlet has a bug present in SQL Server 2008, 2008 R2 and 2012 versions of the cmdlet where T-SQL errors like divide by 0 do not cause an error. I logged a connect item on this and you can see details here:

https://connect.microsoft.com/SQLServer/feedback/details/779320/invoke-sqlcmd-does-not-return-t-sql-errors

Note: the issue is fixed in SQL 2014, however it does not appear a fix has been or will be provided for previous versions.

Answer 2

You are ignoring erros by using -ErrorAction SilentlyContinue, change that to -ErrorAction Stop.

Edit:

Turns out if you want error handling don't use Invoke-SqlCmd (http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx)

You can use GenericSqlQuery function from this answer:Powershell SQL SELECT output to variable which throws errors correctly.