AzureSQL Multiple Invoke-SQLCmd Loop and Connection Errors

Question

I am attempting to loop through an invoke-sqlcmd for multiple AzureSQL databases via Azure Automation. The first item in the loop executes, but the all the rest fail with a:

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)

I am guessing that I need to close the connection from the first invoke-sqlcmd before executing the next, but have not found a direct method to accomplish that with invoke-sqlcmd. Here is my loop:

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
}

My first sql command invocation works. It is likely the connection is still open so the second and subsequent attempts in the loop fail — Kode
– Kode, Commented May 11, 2018 at 20:00
To take control of opening/closing connections, you should use System.Data.SqlClient and use SqlConnection.Open() and SqlConnection.Close(). — anu start
– anu start, Commented May 11, 2018 at 20:37
How would I call invoke-sqlcmd in between those? This is AzureSQL so my invoke-sqlcmd requires the server and database names, so the connection.open seems like double connecting — Kode
– Kode, Commented May 11, 2018 at 20:41

Jacob Colvin · Accepted Answer · 2018-05-16 16:19:07Z

2

You can try making each connection as a powershell job. This solved a very similar issue I had some time ago. Send-MailMessage closes every 2nd connection when using attachments If you want to read an explanation. Basically, if you're unable to use a .Close() method, you can force connections to close by terminating the entire session for each run. In an ideal world the cmdlet would handle all this for you, but not everything was created perfectly.

# 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

}

This is untested since I do not have a server to connect to, but perhaps with a bit of work you can make something out of it.

answered May 16, 2018 at 16:19

Jacob Colvin

2,8651 gold badge22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kode Over a year ago

I am getting a The 'Invoke-Sqlcmd' command was found in the module 'SqlServer', but the module could not be loaded

Jacob Colvin Over a year ago

Strange error since that sounds like an execution policy thing. You may have to import the module at the start of the job. So, -ScriptBlock { Import-Module sqlserver; Write-Output ...

Jacob Colvin Over a year ago

Also make sure your execution policy is set universally - so like, just open any old window and see if you can execute Invoke-Sqlcmd without making any other changes. This is basically what the job is doing. If that doesn't work, you have to change the execution policy.

Kode Over a year ago

Thanks. I updated the SQL Version to 21.0.17262 and that resolved the error, but.... it only executes on the first item in the loop

Jacob Colvin Over a year ago

Could you include the contents of $table in your post? Is it possible that you're creating a connection to a different server on the second iteration? If so, can you make that connection independently?

HariHaran · Accepted Answer · 2018-05-21 05:00:00Z

1

I faced the same issue previously while doing something with the database of azure sql. You can try this

1. Create Automation Account

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

2. Set the Automation account to work with

Set-AzureRmAutomationAccount -Name $automationAccountName -ResourceGroupName $resourceGroupName

3. Create / Import a Runbook

Here we already have a runbook ready so we import it. Here's the runbook code

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. Importing

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

I hope this helps. Instead of using Invoke-Sqlcmd use the $MasterDatabaseCommand.ExecuteNonQuery() like i've provided in the runbook. It will work

answered May 21, 2018 at 5:00

HariHaran

4,4692 gold badges26 silver badges39 bronze badges

2 Comments

Kode Over a year ago

I saw the blog about this. Thanks for passing it along. Can you give an example of what would be in the ConnectionStringValue and Exec Stored Procedure? This failed for me on ExecuteNonQuery

HariHaran Over a year ago

connectionString="Server=(mydb.windows.net);Database=mydb;Integrated Security=True;"

edixon · Accepted Answer · 2018-05-21 07:46:29Z

1

+50

It seems that you append .database.windows.net to the server name inside the loop. I guess that's why it works for the first iteration only.

Just move this line:

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

before this line:

$table | ForEach-Object {

edited May 21, 2018 at 7:46

answered May 19, 2018 at 10:54

edixon

1,0107 silver badges16 bronze badges

3 Comments

Kode Over a year ago

Since Invoke-SQLcmd requires the serverinstance and database names, how can I get around this?

Jacob Colvin Over a year ago

@kode You can't, but you definitely should investigate differences in those variables between different iterations.

Kode Over a year ago

You were right. The loop was appending so each loop was adding a database.windows.net. Once I put it in the right place, it resolved.

Collectives™ on Stack Overflow

AzureSQL Multiple Invoke-SQLCmd Loop and Connection Errors

3 Answers 3

5 Comments

1. Create Automation Account

2. Set the Automation account to work with

3. Create / Import a Runbook

4. Importing

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1. Create Automation Account

2. Set the Automation account to work with

3. Create / Import a Runbook

4. Importing

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related