0

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?

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;")  
  • 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> }. – Banu Akkus Mar 9 '17 at 14:10
  • You tried... the other constructor? – Dan Rediske Mar 10 '17 at 17:23
  • Yes I did and I add my error. It didnt work – Banu Akkus Mar 13 '17 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.