在PowerShell中捕获不同的sqlcmd退出代码以解决连接/数据问题 [英] Capturing in PowerShell different sqlcmd exitcode for connectivity/data issues
问题描述
我正在从PowerShell调用sqlcmd来执行T-SQL脚本.当前,如果由于使用的数据违反约束等导致错误,我将使用:On Error exit"退出脚本.这是通过PowerShell检测$ SqlcmdProcess.ExitCode为1来解决的.
I am calling sqlcmd from PowerShell to execute a T-SQL script. Currently I am using ":On Error exit" to exit the script if there is an error caused by the data used violating a constraint etc. This is handled by PowerShell detecting the $SqlcmdProcess.ExitCode of 1.
但是,如果数据库存在连接问题,则sqlcmd还将ExitCode设置为1.是否可以将:On Error ExitCode设置为非1的方法?我知道可以使用:Exit(SELECT 2)之类的方法来执行此操作,但我还是希望使用:On Error,因此不必重写脚本.
However, if there is a connectivity issue with the database, sqlcmd also gives an ExitCode of 1. Is there a way to set the :On Error ExitCode to something other than 1? I'm aware of using something like :Exit(SELECT 2) to do this, but I'd rather still use :On Error so I don't have to rewrite the script.
推荐答案
您可以在Powershell中使用exit关键字.这是一个例子
You could use the exit keyword in Powershell. Here's an example
创建一个名为sqlcmdexit.ps1的脚本,其内容如下:
Create a script called sqlcmdexit.ps1, with something like the following:
$result = sqlcmd -S"missing" -d master -Q "select @@servername"
if ($result[1] -like "*Error Locating Server/Instance Specified*" -or $result[1] -like "*Could not open a connection to SQL Server*") {
exit 99
}
调用脚本并观察存在的代码:
Call script and observe exist code:
C:\Users\Public\bin>.\sqlcmdExit.ps1
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
C:\Users\Public\bin>$LASTEXITCODE
99
我不知道设置默认ExitCode的任何方法.使用启动过程,您可以执行类似的操作:
I'm not aware of any way to set default ExitCode. Using start-process you could something similar:
$tempFile = [io.path]::GetTempFileName()
$exitCode = (Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
-S"missing" -d master -Q "select @@servername"
"@ -Wait -NoNewWindow -RedirectStandardOutput $tempFile -Passthru).ExitCode
if ($exitCode -eq 1) {
$result = get-content $tempfile
if ($result[1] -like "*Error Locating Server/Instance Specified*" -or $result[1] -like "*Could not open a connection to SQL Server*") {
remove-item $tempFile
Exit 99
}
}
else {
remove-item $tempfile
Exit $exitCode
}
这篇关于在PowerShell中捕获不同的sqlcmd退出代码以解决连接/数据问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!