Powershell的错误检测Invoke-Sqlcmd不总是工作？ [英] Error detection from Powershell Invoke-Sqlcmd not always working?

问题描述

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

我需要能够检测到任何错误还将导致SQL Server Management Studio中的查询完成错误。

以下工作用于检测无效对象错误：

  PS SQLSERVER：\> $ ErrorActionPreference 
停止
 PS SQLSERVER：\> $ Error.Clear（）
 PS SQLSERVER：\> $错误
 PS SQLSERVER：\> Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Queryselect * from doesnotexist-ErrorAction SilentlyContinue 
 PS SQLSERVER：\> $ Error.Exception 
无效的对象名称'doesnotexist'。 
 PS SQLSERVER：\> 
  

为选择1/0执行相同操作不 work：

  PS SQLSERVER：\> $ ErrorActionPreference 
停止
 PS SQLSERVER：\> $ Error.Clear（）
 PS SQLSERVER：\> $错误
 PS SQLSERVER：\> Invoke-Sqlcmd -ServerInstance .\SQLEXPRESS -Database Test -Queryselect 1/0-ErrorAction SilentlyContinue 
 PS SQLSERVER：\> $ Error.Exception 
 PS SQLSERVER：\> 
  

我希望这会像SSMS一样导致除以零错误遇到错误。

没有检测到这个特定的错误让我想知道其他错误是否也会被检测不到。

是一个事情，我如何确保将检测到所有错误？

更新

事实证明，我没有在我正在安装的服务器上使用Invoke-Sqlcmd，所以第二个想法是我必须使用sqlcmd.exe。

我认为这对我有用：

  $ tempfile = [io.path] :: GetTempFileName （）
 $ cmd = [string] :: Format（sqlcmd -S {0} -U {1} -P {2} -d {3} -i {4} -b> $ tempfile ，
 $ g_connectionstring数据源，
 $ g_connectionstring用户ID，
 $ g_connectionstring密码，
 $ g_connectionstring初始目录，
 $ path）
 Invoke-Expression -Command $ cmd 
 if（ $ LASTEXITCODE）
 {
 $ err = Get-Content $ tempfile | Out-String 
 Corax-MessageSQLError$ err 
 exit 
} 
删除项$ tempfile 
  pre> 
 
解决方案
无论ErrorAction设置如何，invoke-sqlcmd cmdlet都存在SQL Server 2008,2008 R2和2012版本中的错误的T-SQL错误除以0的cmdlet不会导致错误。我在此登录了一个连接项，您可以在这里查看详细信息：
 
 
 
  https://connect.microsoft.com/SQLServer/feedback/details/779320/invoke-sqlcmd-does-not-return-t-sql -errors  
 
 
 
注意：该问题在SQL 2014中已修复，但是它不会出现已经或将要为以前版本提供的修补程序。 p> 
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

解决方案
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.

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