How to change CommandTimeout of ExecutionBlock.ExecuteBlock

Bastiaan Molsbeck

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.

Rob C

Hi,

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.

Bastiaan Molsbeck

Thank you for your reply.

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.

Rob C

We've raised a bug report, with reference CSD-208. Whether this gets implemented or not will be down to the project managers. Even if the decision is made to implement it, it's difficult to know how long it would take to implement.

Sorry that I can't be more specific.

Bastiaan Molsbeck

Hi Robert,
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.

Rob C

Hi 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.

Bastiaan Molsbeck

Hi Robert,

Is there any news to report about bug with reference CSD-208 ?

Kind regards,
Bastiaan.

Rob C

Hi 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.