powershell - Azure SQL 多次调用-SQLCmd 循环和连接错误

Question

我正在尝试通过 Azure 自动化为多个 AzureSQL 数据库循环 invoke-sqlcmd。循环中的第一项执行，但所有其余项都失败并显示:

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

我猜测我需要在执行下一个之前关闭与第一个 invoke-sqlcmd 的连接，但还没有找到使用 invoke-sqlcmd 完成该操作的直接方法。这是我的循环:

param(      
# Parameters to Pass to PowerShell Scripts
[parameter(Mandatory=$true)][String] $azureSQLServerName = "myazuresql",        
[parameter(Mandatory=$true)][String] $azureSQLCred = "myazureautosqlcred"        
        )

# DB Name Array
$dbnamearray = @("database1","database2","database3")
$dbnamearray

# Datatable Name
$tabName = "RunbookTable"

#Create Table object
$table = New-Object system.Data.DataTable "$tabName"

#Define Columns
$col1 = New-Object system.Data.DataColumn dbname,([string])

#Add the Columns
$table.columns.add($col1)

# Add Row and Values for dname Column
ForEach ($db in $dbnamearray) 
{
$row = $table.NewRow()
$row.dbname = $db
#Add the row to the table
$table.Rows.Add($row)
}

#Display the table
$table | format-table -AutoSize

# Loop through the datatable using the values per column 
$table | ForEach-Object {

# Set loop variables as these are easier to pass then $_.
$azureSQLDatabaseName = $_.dbname

# Execute SQL Query Against Azure SQL
$azureSQLServerName = $azureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name $azureSQLCred
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $azureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $azureSQLDatabaseName -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput
}

Answer 1

您可以尝试将每个连接设为 powershell job .这解决了我前段时间遇到的一个非常相似的问题。 Send-MailMessage closes every 2nd connection when using attachments如果你想阅读解释。基本上，如果您无法使用 .Close() 方法，您可以通过终止每次运行的整个 session 来强制关闭连接。在理想情况下，该 cmdlet 会为您处理所有这些，但并非一切都完美无缺。

# Loop through the datatable using the values per column 
$table | ForEach-Object {

# Set loop variables as these are easier to pass then $_.
$azureSQLDatabaseName = $_.dbname

# Execute SQL Query Against Azure SQL
$azureSQLServerName = $azureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name $azureSQLCred

# Pass in the needed parameters via -ArgumentList and start the job.
Start-Job -ScriptBlock { Write-Output $(Invoke-Sqlcmd -ServerInstance $args[0] -Username $args[1].UserName -Password $args[1].GetNetworkCredential().Password -Database $args[0] -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 } -ArgumentList $azureSQLServerName, $Cred | Wait-Job | Receive-Job

}

这是未经测试的，因为我没有要连接的服务器，但也许通过一些工作你可以从中得到一些东西。