Post reply

Scheduled Jobs in Azure SQL Databases Failing for Auto-Paused DB

  • May 24, 2023 at 9:21 pm

    #4196827

    Hello!

    I have somewhat similar kind of PowerShell script which executes the stored procedure on a schedule. Since the database is set to auto-pause, job is failing at times with the error message below:

    "Exception calling "Open" with "0" argument(s): on server is not currently available. Please retry the connection later. "

    How can I add waitfor delay in the script below so that it will wait for sometime before executing the SP.

     

    -------------------------------------------------------------------------------------------------------------------------------------------------------

    Import-AzureRmContext -Path "C:\azure-credential.json"

    # select the "Sunrise Strong" subscription

    Select-AzureRmSubscription -SubscriptionName "Sunrise Strong"

    # select the sunrisestrong_dw data warehouse

    $database = Get-AzureRmSqlDatabase –ResourceGroupName "sunrisestrong" –ServerName "sunrisestrong" –DatabaseName "sunrisestrong_dw"

    # resume data warehouse. the command won't fail if database warehouse is already online

    $resultDatabase = $database | Resume-AzureRmSqlDatabase

    # scale to DW600

    $database | Set-AzureRmSqlDatabase -RequestedServiceObjectiveName "DW600"

    # exeucte stored procedure dbo.your_procedure without parameters

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Server=sunrisestrong.database.windows.net;Database=sunrisestrong_dw;UID=your_user_name;Password=your_password"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = "dbo.your_procedure"

    $SqlCmd.Connection = $SqlConnection

    # depending on whether the SQL command returns data, use one of the two code blocks, not both

    # if (the stored procedure returns data) {

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet

    $SqlAdapter.Fill($DataSet)

    # } else {

    $SqlConnection.Open()

    $SqlCmd.ExecuteNonQuery()

    # }

    $SqlConnection.Close()

    $DataSet.Tables[0]

     

     

    Thanks in advance.

  • May 24, 2023 at 10:54 pm

    #4196874

    what is the aversion at googling before posting? a simple google for "powershell wait" returns you the command you need to use for it.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply