We often encounter support cases where our customers leave the query execution timeout value at its default. In certain situations, we may find that when this value is reached, the application reports an error and does not continue. In this case, I would like to share an example implemented in PowerShell that allows incrementing the command timeout value up to 5 attempts, with a 5-second margin in each operation.
The PowerShell code is as follows:
# Database connection parameters
$serverName = "ServerName.database.windows.net"
$databaseName = "DBName"
$username = "UserName"
$password = "Password"
# Connection string for SQL Login authentication
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$username;Password=$password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
# Log file path
$logFile = "C:\Temp\File.log"
# Set the initial timeout value
$timeout = 5
Function GiveMeConnectionSource($connectionString)
{
$NumberAttempts= 5
for ($i=1; $i -le [int]$NumberAttempts; $i++)
{
try
{
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $connectionString
$start = get-date
$SQLConnection.Open()
$end = get-date
Write-Host "Connected to the database in (ms):" +(New-TimeSpan -Start $start -End $end).TotalMilliseconds + " - ID:" + $SQLConnection.ClientConnectionId.ToString() + " -- HostName: " + $SQLConnection.WorkstationId + " Server Version:" + $SQLConnection.ServerVersion
return $SQLConnection
break;
}
catch
{
Write-Host "Not able to connect - Retrying the connection..." + $Error[0].Exception.ErrorRecord + "-" + $Error[0].Exception.ToString().Replace("\t"," ").Replace("\n"," ").Replace("\r"," ").Replace("\r\n","").Trim()
Write-Host "Waiting for next retry in 5 seconds .."
Start-Sleep -s 5
}
}
}
# Function to execute the SQL command with retry logic
function ExecuteWithRetry {
param(
[int]$retryCount = 0,
[System.Data.SqlClient.SqlConnection] $connection,
[string]$query
)
try {
# Open the database connection
# Create a SqlCommand object
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
# Set the initial timeout
$command.CommandTimeout = ($timeout * $retryCount)
# Execute the SQL command
$Null = $command.ExecuteNonQuery()
}
catch [System.Data.SqlClient.SqlException] {
# Handle timeout errors
if ($_.Exception.Number -eq -2) {
Write-Host "A timeout error has occurred. Increasing CommandTimeout..."
}
else {
Write-Host "SQL Error occurred: $($_.Exception.Message)"
}
# Retry the operation if the maximum number of retries is not reached
if ($retryCount -lt 5) {
$retryCount++
Write-Host "Retrying operation (Retry count: $retryCount)..."
ExecuteWithRetry -retryCount $retryCount
}
else {
Write-Host "Maximum number of retries reached. Exiting..."
}
}
catch {
Write-Host "An error occurred: $($_.Exception.Message)"
}
}
# Execute the SQL command with retry logic
$connection = GiveMeConnectionSource $connectionString
if($connection -eq $null)
{
Write-Host "It is not possible to connect to the database"
exit;
}
ExecuteWithRetry 1 $connection "WAITFOR DELAY '00:00:40'"
$connection.Close()
# Log the operation to the log file
Add-Content -Path $logFile -Value "Executed command: $query"
Add-Content -Path $logFile -Value "Timeout: $timeout seconds"
Add-Content -Path $logFile -Value "---------------------------------------"
Let's go more deeper about the PowerShell Script:
# Database connection parameters
$serverName = "servername.database.windows.net"
$databaseName = "dbName"
$username = "UserName"
$password = "Password"
These variables store the necessary information for the database connection: the server name, database name, username, and password. Modify these values with the appropriate credentials for your database.
# Connection string for SQL Login authentication
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$username;Password=$password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
This line constructs the connection string using the provided connection parameters. It uses SQL Login authentication and sets the connection timeout to 30 seconds.
# Log file path
$logFile = "C:\Temp\File.log"
This variable specifies the file path for the log file where the script will write the operation details.
# Set the initial timeout value
$timeout = 5
The script sets an initial timeout value of 5 seconds. This timeout will be increased in case of a timeout error during the execution of the SQL command.
Function GiveMeConnectionSource($connectionString)
{
$NumberAttempts= 5
for ($i=1; $i -le [int]$NumberAttempts; $i++)
{
try
{
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $connectionString
$start = get-date
$SQLConnection.Open()
$end = get-date
Write-Host "Connected to the database in (ms):" +(New-TimeSpan -Start $start -End $end).TotalMilliseconds + " - ID:" + $SQLConnection.ClientConnectionId.ToString() + " -- HostName: " + $SQLConnection.WorkstationId + " Server Version:" + $SQLConnection.ServerVersion
return $SQLConnection
break;
}
catch
{
Write-Host "Not able to connect - Retrying the connection..." + $Error[0].Exception.ErrorRecord + "-" + $Error[0].Exception.ToString().Replace("\t"," ").Replace("\n"," ").Replace("\r"," ").Replace("\r\n","").Trim()
Write-Host "Waiting for next retry in 5 seconds .."
Start-Sleep -s 5
}
}
}
This is a function named GiveMeConnectionSource. It takes the connection string as a parameter. The function attempts to establish a connection to the database using a loop that allows for a maximum of 5 connection attempts.
Inside the loop, it tries to open the SQL connection and measures the time it takes to establish the connection. If the connection is successful, it prints the connection details and returns the SQL connection object. If an exception occurs, indicating a connection error, it displays an error message, waits for 5 seconds, and then retries the connection.
# Function to execute the SQL command with retry logic
function ExecuteWithRetry {
param(
[int]$retryCount = 0,
[System.Data.SqlClient.SqlConnection] $connection,
[string]$query
)
try {
# Open the database connection
# Create a SqlCommand object
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
# Set the initial timeout
$command.CommandTimeout = ($timeout * $retryCount)
# Execute the SQL command
$Null = $command.ExecuteNonQuery()
}
catch [System.Data.SqlClient.SqlException] {
# Handle timeout errors
if ($_.Exception.Number -eq -2) {
Write-Host "A timeout error has occurred. Increasing CommandTimeout..."
}
else {
Write-Host "SQL Error occurred: $($_.Exception.Message)"
}
# Retry the operation if the maximum number of retries is not reached
if ($retryCount -lt 5) {
$retryCount++
Write-Host "Retrying operation (Retry count: $retryCount)..."
ExecuteWithRetry -retryCount $retryCount
}
else {
Write-Host "Maximum number of retries reached. Exiting..."
}
}
catch {
Write-Host "An error occurred: $($_.Exception.Message)"
}
}
This is the ExecuteWithRetry function responsible for executing the SQL command with retry logic. It takes three parameters: retryCount (the number of retries attempted), connection (the SQL connection object), and query (the SQL command to execute).
Inside the function, a new SqlCommand object is created using the provided query and connection. The command timeout is set to the current timeout value, which is incremented with each retry.
The SQL command is executed using the ExecuteNonQuery method. If a SqlException occurs, the function checks if the error number is -2, indicating a timeout error. If it is, the timeout is increased. Otherwise, it displays the error message.
If the maximum number of retries (5) is not reached, the function recursively calls itself with an incremented retry count to retry the operation. Otherwise, it displays a maximum retries reached message.
# Execute the SQL command with retry logic
$connection = GiveMeConnectionSource $connectionString
if($connection -eq $null)
{
Write-Host "It is not possible to connect to the database"
exit;
}
ExecuteWithRetry 1 $connection "WAITFOR DELAY '00:00:40'"
$connection.Close()
In this section, the script invokes the GiveMeConnectionSource function to get the SQL connection object. If the connection object is null, it means the connection could not be established, and the script displays an error message and exits.
Next, the ExecuteWithRetry function is called with a retry count of 1, the obtained SQL connection, and a sample SQL command (WAITFOR DELAY '00:00:40'). You can replace this command with your own SQL command.
# Log the operation to the log file
Add-Content -Path $logFile -Value "Executed command: $query"
Add-Content -Path $logFile -Value "Timeout: $timeout seconds"
Add-Content -Path $logFile -Value "---------------------------------------"
This part adds the executed command and timeout information to the log file specified by the $logFile variable.
Enjoy!
Updated Jun 16, 2023
Version 2.0
Jose_Manuel_Jurado
Microsoft
Microsoft
