powershell：使用-InputFile而不是-Query时，invoke-sqlcmd捕获错误 [英] powershell: invoke-sqlcmd catching errors when using -InputFile instead of -Query

问题描述

我有一个powershell脚本，它从一个给定的文件夹执行sql脚本。这是工作，但如果出现错误，我没有看到错误的原因。在某些情况下，显示错误，当powershell本身抛出错误。但是，当一个更为简单的故障如主键冲突时，就不会出现任何错误。

我已经尝试了以下方式，但是使用-InputFile参数时不起作用。将通过-Query参数替换此错误并显示错误。

  try 
 {
 sqlps Invoke-Sqlcmd -ServerInstance$ dbInstance-Database$ dbName -InputFile$ updateScriptsFolder\ $ updateSql-username $ username -password $ password -ErrorAction'Stop'-Verbose -OutputSqlErrors 1 
} 
 catch 
 {
写入主机执行$ updateScriptsFolder\ $ updateSql时出错-foregroundcolor red 
写入主机$ error -foregroundcolor red 
} 
  

现在唯一的解决方案是将错误处理添加到每个sql脚本中。但很可能，开发人员忘记在脚本中添加错误处理。我想要一个更一般的错误处理。谢谢，
Michael

解决方案

根据上述注释，将该文件读入一个字符串变量，并使用该变量作为-query参数的值。

I have an powershell script which is executing sql scripts from a given folder. This is working, but in case of error I don't see the reason of the error. In some cases the error is displayed, when the powershell itself throws the error. But when it's a more simple failure such as a primary key conflict, no error is shown.

I've tried it the following way, but this does not work when using the "-InputFile" parameter. Replacing this by the "-Query" Parameter and the error will be shown.

try 
{
    sqlps Invoke-Sqlcmd -ServerInstance "$dbInstance" -Database "$dbName" -InputFile "$updateScriptsFolder\$updateSql" -username $username -password $password -ErrorAction 'Stop' -Verbose -OutputSqlErrors 1
}
catch
{
    Write-Host "Error while executing $updateScriptsFolder\$updateSql" -foregroundcolor red
    Write-Host $error -foregroundcolor red
}

The only solution for now is to add the error handling to every sql script. But it's very likely that developers forget to add the errorhandling to their script. I want a more general error handling. Can anybody help?

Thanks, Michael

解决方案

As per the comments above, read the file into a string variable and use that variable as the value for the -query parameter.

这篇关于powershell：使用-InputFile而不是-Query时，invoke-sqlcmd捕获错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！