CODEWITHSUNDEEP
sql-serverpowershellazure-automation
Stpete111
Stpete111

Reputation: 3447

Powershell Script in Azure Automation to run SQL Server stored procedure - possible timeout issue

We use Azure Automation to execute Azure SQL Server stored procedures on a schedule.

Here is one of the scripts:

workflow ExecuteSP1
{
    Write-Output "JOB START BEFORE INLINESCRIPT"

    inlinescript
    {
        Write-Output "JOB START"
        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Connection String"
        $MasterDatabaseConnection.Open()

        Write-Output "CONNECTION OPEN"

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "dbo.StoredProcedure1"

        Write-Output "DATABASE COMMAND TEXT ASSIGNED"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        Write-Output "EXECUTING QUERY"

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 

        Write-Output "CONNECTION CLOSED"
    }    
    Write-Output "WORK END - AFTER INLINESCRIPT"

When checking the success logs, Azure Automation informs us that this script runs successfully, every time it runs. However, the resulting effects in our SQL Server are not reflecting as having occurred. So I can only imagine this means the stored procedure is timing out. I have confirmed that the SP runs correctly when I run it directly on the SQL Server, but it does take a long time to run.

Is there anything I can add to the above script that will:

  1. Increase any timeout period to infinite or at least maybe 4 hours, either in the script, or on the SQL Server side?

  2. Have an error actually get thrown in AA if the stored procedure does not finish execution with success?

Upvotes: 0

Views: 1214

Answers (1)

Subjective Reality
Subjective Reality

Reputation: 158

You can do a couple of things. First, you can specify your command timeout:

$MasterDatabaseCommand.CommandTimeout = 18000

Next, you can wrap in a try catch, though I am not certain of exactly how Azure Automation would handle it:

try {
  # Execute the query
  $MasterDatabaseCommand.ExecuteNonQuery()

  Write-Output "EXECUTING QUERY"
}
catch {
  Write-Error $Error[0].Exception.Message
  # or write to your log file or do what you need to here
}

Upvotes: 1

Related Questions