title: AzureSQL Multiple Invoke-SQLCmd Loop and Connection Errors
body: <p>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:</p>
  
  <blockquote>
    <p>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)</p>
  </blockquote>
  
  <p>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:</p>
  
  <pre><code>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&gt;&amp;1
  Write-Output $SQLOutput
  }
  </code></pre>