Azure Sql Update Statistic Using Powershell

Question

I want to update my azure sql statistics using azure automation with powershell script.

workflow Runbook-UPDATESTATS
{
 param(
 [parameter(Mandatory=$True)]
 [string] $SqlServer,
 [parameter(Mandatory=$True)]
 [string] $Database,
 [parameter(Mandatory=$True)]
 [string] $SQLCredentialName 
 )
 # Get the stored username and password from the Automation credential
 $SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
 if ($SqlCredential -eq $null)
 {
 throw "Could not retrieve '$SQLCredentialName' credential asset. Check that you created this first in the Automation service."
 }
 $SqlUsername = $SqlCredential.UserName 
 $SqlPass = $SqlCredential.GetNetworkCredential().Password
 $TableNames = Inlinescript {
 # Define the connection to the SQL Database
 $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
 # Open the SQL connection
 $Conn.Open() 
 # Return the tables 
 $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
 $Cmd.CommandTimeout=120 
 # Get the list of tables 
 $SQLCommandString = @"
 SELECT '['+s.name +'].[' + t.name + ']' AS TableName
 FROM sys.tables t
 join sys.schemas s on t.schema_id = s.schema_id
"@
 $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
 $Cmd.CommandTimeout=120
 # Execute the SQL command
 $TableSchema =New-Object system.Data.DataSet
 $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
 [void]$Da.fill($TableSchema)
 $Conn.Close()
 }
 # Interate through tables 
 ForEach ($TableName in $TableNames)
 {
 Write-Verbose "Creating checkpoint"
 Checkpoint-Workflow
 Write-Verbose "Updating Table $TableName..."
 InlineScript {
 $SQLCommandString = @"
 EXEC('UPDATE STATISTICS $Using:TableName WITH FULLSCAN')
"@
 # Define the connection to the SQL Database
 $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
 # Open the SQL connection
 $Conn.Open()
 # Define the SQL command to run. In this case we are getting the number of rows in the table
 $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
 # Set the Timeout to be less than 30 minutes since the job will get queued if > 30
 # Setting to 25 minutes to be safe.
 $Cmd.CommandTimeout=1500
 # Close the SQL connection
 $Conn.Close()
 } 
 }
 Write-Verbose "Finished Updating"
}

Here is my error:

*Exception calling "Open" with "0" argument(s): "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: SQL Network Interfaces, error: 25 - Connection string is not valid)" At Runbook-UPDATESTATS:26 char:26 + + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Exception calling "Fill" with "1" argument(s): "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: SQL Network Interfaces, error: 25 - Connection string is not valid)" At Runbook-UPDATESTATS:26 char:26 + + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException*

Is there anything wrong in my code? Any suggestion to resolve this issue?

Answer 1

The error indicates that your connection string is wrong. Verify that your string concatenates correctly to what you're expecting.(I'm also not familiar with your use of credential, so that may cause issues if it doesn't expose the password in the way you're expecting. You may want to consider the constructor that takes a credential object as an argument)

$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;") 

Looks like it might need to be

$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$($SqlServer),$($SqlServerPort);Database=$($Database);User ID=$($SqlUsername);Password=$($SqlPass);Trusted_Connection=False;Encrypt=True;Connection Timeout=30;") 

• Banu Akkus: I tried but didnt work. BadRequest: The Runbook definition is invalid. Method invocation is not supported in a Windows PowerShell Workflow. To use .NET scripting, place your commands in an inline script: InlineScript { <commands> }. Method invocation is not supported in a Windows PowerShell Workflow. To use .NET scripting, place your commands in an inline script: InlineScript { <commands> }.
• Dan Rediske: You tried... the other constructor?
• Banu Akkus: Yes I did and I add my error. It didnt work