powershell：使用 -InputFile 而不是 -Query 时调用 sqlcmd 捕获错误
我有一个 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
{
Write-Host "Error while executing $updateScriptsFolder\$updateSql" -foregroundcolor red
Write-Host $error -foregroundcolor red
}
目前唯一的解决方案是为每个 sql 脚本添加错误处理。但开发人员很可能忘记将错误处理添加到他们的脚本中。我想要更通用的错误处理。有人可以帮忙吗？
谢谢， 迈克尔
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据上面的注释，将文件读入字符串变量并使用该变量作为 -query 参数的值。
As per the comments above, read the file into a string variable and use that variable as the value for the -query parameter.
当使用
-InputFile而不是-Query时，我遇到了同样的错误，但这不是实际的问题。问题是我错误地尝试为-TrustServerCertificate设置值 (false)。删除该值后，-InputFile开始正常工作。I got this same error when using
-InputFileinstead of-Query, but that wasn't the actual problem. The problem was that I had been improperly attempting to set a value (false) for-TrustServerCertificate. Once I removed that value,-InputFilestarted working just fine.