Error detection from Powershell Invoke-Sqlcmd not always working?

16,156

Solution 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.

Solution 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.

Solution 3

As already mentioned in another answer by Raf; main reason for not detecting the error is -ErrorAction SilentlyContinue (cause you are bypassing the error). If you even omit this option altogether; you will see the expected error

Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Query "select 1/0"

You can as well use other error option like

Stop running on an error -AbortOnError

Display errors -OutputSqlErrors

It's all mentioned in relevant MSDN document. take a look Invoke-Sqlcmd cmdlet

Share:
16,156

Related videos on Youtube

Simon de Kraa
Author by

Simon de Kraa

Updated on September 14, 2022

Comments

  • Simon de Kraa
    Simon de Kraa about 1 year

    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