Ошибки многократного цикла Invoke-SQLCmd и подключения в AzureSQL

Я пытаюсь выполнить цикл invoke-sqlcmd для нескольких баз данных AzureSQL через службу автоматизации Azure. Выполняется первый элемент в цикле, но все остальные завершаются ошибкой:

Invoke-Sqlcmd: при установке соединения с SQL Server произошла ошибка, связанная с сетью или конкретным экземпляром. Сервер не найден или не был доступен. Убедитесь, что имя экземпляра правильное и что SQL Server настроен на разрешение удаленных подключений. (поставщик: поставщик именованных каналов, ошибка: 40 - не удалось открыть соединение с 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
}

powershell azure-sql-database invoke-sqlcmd azure-automation
person Kode    schedule 11.05.2018    source источник
comment
Сначала ознакомьтесь с этой ссылкой ссылка.   -  person anu start    schedule 11.05.2018
comment
Мой первый вызов команды sql работает. Скорее всего, соединение все еще открыто, поэтому вторая и последующие попытки в цикле завершатся ошибкой.   -  person Kode    schedule 11.05.2018
comment
Чтобы управлять открытием / закрытием соединений, вы должны использовать System.Data.SqlClient и использовать SqlConnection.Open() и SqlConnection.Close().   -  person anu start    schedule 11.05.2018
comment
Как мне вызвать invoke-sqlcmd между ними? Это AzureSQL, поэтому моему invoke-sqlcmd требуются имена сервера и базы данных, поэтому connection.open выглядит как двойное соединение   -  person Kode    schedule 11.05.2018

Ответы (3)


arrow_upward
2
arrow_downward

Вы можете попробовать установить каждое соединение как работа в PowerShell. Это решило очень похожую проблему, которая была у меня некоторое время назад. Send-MailMessage закрывает каждое второе соединение при использовании вложений Если хотите прочитать объяснение. По сути, если вы не можете использовать метод .Close (), вы можете принудительно закрывать соединения, завершая весь сеанс для каждого запуска. В идеальном мире командлет справился бы со всем этим за вас, но не все было создано идеально.

# 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

}

Это не проверено, поскольку у меня нет сервера для подключения, но, возможно, немного поработав, вы сможете что-то из этого сделать.

person Jacob Colvin    schedule 16.05.2018
comment
Я получаю команду «Invoke-Sqlcmd» была найдена в модуле «SqlServer», но модуль не может быть загружен - person Kode; 18.05.2018
comment
Странная ошибка, потому что это похоже на политику выполнения. Возможно, вам придется импортировать модуль в начале работы. Таким образом, 1_ ... - person Jacob Colvin; 18.05.2018
comment
Также убедитесь, что ваша политика выполнения установлена ​​универсально - например, просто откройте любое старое окно и посмотрите, сможете ли вы выполнить Invoke-Sqlcmd, не внося никаких других изменений. Это в основном то, чем занимается работа. Если это не сработает, вам нужно изменить политику выполнения. - person Jacob Colvin; 18.05.2018
comment
Спасибо. Я обновил версию SQL до 21.0.17262, и это устранило ошибку, но .... она выполняется только для первого элемента в цикле. - person Kode; 18.05.2018
comment
Не могли бы вы включить в свой пост содержимое таблицы $ table? Возможно ли, что вы создаете соединение с другим сервером на второй итерации? Если да, можете ли вы установить это соединение самостоятельно? - person Jacob Colvin; 21.05.2018

arrow_upward
1
arrow_downward

Раньше я сталкивался с той же проблемой, когда что-то делал с базой данных azure sql. Вы можете попробовать это

1. Создайте учетную запись службы автоматизации.

New-AzureRmAutomationAccount -ResourceGroupName $resourceGroupName -Name $automationAccountName -Location $location

2. Настройте учетную запись службы автоматизации для работы с

Set-AzureRmAutomationAccount -Name $automationAccountName -ResourceGroupName $resourceGroupName

3. Создайте / импортируйте Runbook.

Здесь у нас уже есть готовый модуль Runbook, поэтому мы его импортируем. Вот код Runbook

workflow runbookNameValue
{
    inlinescript
    {
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "ConnectionStringValue"

        # Open connection to Master DB
        $MasterDatabaseConnection.Open()

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "Exec stored procedure"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 
    }
}

4. Импорт

Import-AzureRMAutomationRunbook -Name $runBookName -Path $scriptPath -ResourceGroupName $resourceGroupName -AutomationAccountName $automationAccountName -Type PowerShell

Надеюсь, это поможет. Вместо использования Invoke-Sqlcmd используйте $MasterDatabaseCommand.ExecuteNonQuery(), как я указал в модуле Runbook. Это будет работать

person HariHaran    schedule 21.05.2018
comment
Я видел блог об этом. Спасибо, что передали это. Можете ли вы привести пример того, что будет в хранимой процедуре ConnectionStringValue и Exec? Это не удалось для меня на ExecuteNonQuery - person Kode; 21.05.2018
comment
connectionString = Server = (mydb.windows.net); База данных = mydb; Встроенная безопасность = True; - person HariHaran; 22.05.2018

arrow_upward
1
arrow_downward

Кажется, что вы добавляете .database.windows.net к имени сервера внутри цикла. Думаю, поэтому это работает только для первой итерации.

Просто переместите эту строку:

$azureSQLServerName = $azureSQLServerName + ".database.windows.net"

перед этой строкой:

$table | ForEach-Object {
person edixon    schedule 19.05.2018
comment
Поскольку Invoke-SQLcmd требует имена экземпляров сервера и базы данных, как я могу обойти это? - person Kode; 19.05.2018
comment
@kode Вы не можете, но вам определенно следует изучить различия в этих переменных между разными итерациями. - person Jacob Colvin; 21.05.2018
comment
Ты был прав. Цикл добавлялся, поэтому каждый цикл добавлял database.windows.net. Как только я поместил его в нужное место, он разрешился. - person Kode; 21.05.2018