来自 Powershell Invoke-Sqlcmd 的错误检测并不总是有效? [英] Error detection from Powershell Invoke-Sqlcmd not always working?

问题描述

通过执行位于文件夹中的查询列表来更新数据库.

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

我需要能够检测任何错误，这些错误也会导致 SQL Server Management Studio 中的查询已完成，但有错误".

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:>

对 select 1/0 做同样的事情不起作用:

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:>

我希望这会导致遇到被零除错误"错误，就像在 SSMS 中一样.

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?

更新

事实证明，我正在安装的服务器上没有可用的 Invoke-Sqlcmd，所以我再次想到我必须使用 sqlcmd.exe.

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.

我认为这对我有用:

$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

推荐答案

无论 ErrorAction 设置如何，invoke-sqlcmd cmdlet 在 SQL Server 2008、2008 R2 和 2012 版本的 cmdlet 中都存在一个错误，其中 T-SQL 错误如除以 0 不会导致错误.我在此记录了一个连接项目，您可以在此处查看详细信息:

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

注意:该问题已在 SQL 2014 中修复，但似乎没有为以前的版本提供或将提供修复.

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

这篇关于来自 Powershell Invoke-Sqlcmd 的错误检测并不总是有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！