1

I have created a run-book in azure to automate my sproc execution on a scheduled interval, i see a timeout exception while the sproc gets executed.

I have tried changing the connection timeout value in my connection string to "0" to make it indefinite but I still see the same issue reoccurring. When I execute this stored procedure it hardly takes 3 minutes but the run-book fails with timeout exception.Can someone help me if I am missing anything.

Runbook type : PowerShell Workflow Runbook

Thanks, Manoj.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Manoj
  • 61
  • 6

1 Answers1

1

Setting your connection string timeout to 0 will only affect the connection timeout, in other words you are giving your process infinite time to establish a connection with the database. Without seeing your code, I suspect the issue is with your command timeout - your stored proc is likely taking longer than the default 30s to complete. Before executing the stored proc you should set an appropriate timeout on the SqlCommand instance:

# Create command with a 10 minute timeout
$DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$DatabaseCommand.Connection = $DatabaseConnection
$DatabaseCommand.CommandTimeout = 600
$DatabaseCommand.CommandText = "..."

# Execute non query
$NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
RevBB
  • 11
  • 1