How to change CommandTimeout of ExecutionBlock.ExecuteBlock
Bastiaan Molsbeck
Posts: 73
Hi,
I'm using the SQL Comparison SDK to periodically synchronize the data of a production database with a test database.
Sometimes when there are a lot of changes I get the following error:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(ExecutionBlock block, DbConnection connection)
After investigation and viewing the code of the method "RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock" using the .NET Reflector I see that this method uses a hard-coded CommandTimeout of 6000 (seconds), which is about 1 hour and 40 minutes.
I would like to know how I can raise this timeout, because the generated script takes more than 6000 seconds to be executed.
A different solution would be not to synchronize the entire database in one batch, but that would mean that I have to alter the way my application works. I was hoping for a different solution, like a using an application configuration file or similar.
PS: I'm using the lastest version of the SDK, v11.2.
I'm using the SQL Comparison SDK to periodically synchronize the data of a production database with a test database.
Sometimes when there are a lot of changes I get the following error:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(ExecutionBlock block, DbConnection connection)
After investigation and viewing the code of the method "RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock" using the .NET Reflector I see that this method uses a hard-coded CommandTimeout of 6000 (seconds), which is about 1 hour and 40 minutes.
I would like to know how I can raise this timeout, because the generated script takes more than 6000 seconds to be executed.
A different solution would be not to synchronize the entire database in one batch, but that would mean that I have to alter the way my application works. I was hoping for a different solution, like a using an application configuration file or similar.
PS: I'm using the lastest version of the SDK, v11.2.
Comments
Unfortunately at the moment there is no way to change the timeout value. A workaround would be to use the SDK to generate a deployment script and then execute it using a SqlCommand object. The SqlCommand object has a CommandTimeout property which you can set to whichever value you like.
I hope this helps.
Redgate Software
Yes, by first generating the script and then executing it manually I can set my own timeout.
What I would like to know is whether it will be possible in a future release of the SDK to customize the timeout.
Or is this something that is not considered important to you?
Regards.
Sorry that I can't be more specific.
Redgate Software
I would like to know the status of bug with reference CSD-208.
It's over a year since this bug was reported. I was wondering if there is any news regarding whether this bug will be solved in any future version.
Kind regards,
Bastiaan.
Apologies, the bug has not yet been fixed. I've flagged it for review so its priority should be reassessed when the next triage meeting happens.
Redgate Software
Is there any news to report about bug with reference CSD-208 ?
Kind regards,
Bastiaan.
Sorry but it's unlikely that this will be done in the near future. Very few people are affected by this so it has a fairly low priority I'm afraid. I will let you know as soon as the situation changes.
Apologies.
Redgate Software