Mary Chipman [MSFT]
1/22/2008 2:02:00 PM
A couple of questions:
Why aren't you using SqlClient if you're going against a SQL Server?
Why don't you wrap //my long running procedures inside of a stored
procedure BEGIN TRAN/COMMIT TRAN? That way you can handle all of the
errors on the server in addition to getting better performance. Each
separate procedure call from the client is another round trip over the
network.
--Mary
On Mon, 21 Jan 2008 10:53:27 +0300, "serg" <serg_NOSPAM_AT_alef.ru>
wrote:
>Hi, Mary
>
>It is an accounting procedure that is closin finacial year.
>There are not only simple insertion/deletetion but table triggers firing and
>stored procedure execution.
>I am widly using IDbCommand.CommandTimeout property for longrunning
>procedures and it work fine.
>But at the very and when i am closing transaction i get an exception.
>This is my code snippet
>
> public class MyConnMgmtClass : IDisposable
>
> {
>
> .
>
> private IDbTransaction _transaction;
>
> .
>
> public void Commit ()
>
> {
>
> if (_transaction == null)
>
> throw new AlefException (00013, null);
>
> _transaction.Commit(); // at this string occurs timeout
>exception
>
> }
>
> .
>
>}
>
>
>
>Timeout exception occurs at the client level in about 30sec.
>Ant this is only client exception not server because of at the server level
>i see that transaction is successfuly commited.
>I have profiled SQL several times and I see that duration for commiting
>transaction is between 60sec to 90sec. (this is mssql2005)
>
>Now i have found the only workaround.
>Do not use IDbTransaction object.
>Instead I am using excplicit transaction opening/closing with IDbCommand
>objects.
>Code like this:
>
>
>IDbCommand command = conn.CreateCommand(false);
>command.CommandText = "BEGIN TRAN";
>command.ExceuteNonQuery();
>...
>// my long running procedures
>...
>command.CommandTimeout = 180; //seconds
>command.CommandText = "COMMIT TRAN";
>command.ExceuteNonQuery();
>
>
>
>This is working.
>3 minutes is long enough to commit my transaction.
>I do not understand why IDbTransaction object does not have public Timeout
>property like IDbCommand object.
>
>Thanks a lot
>Serg.
>
>
>
>
>
>"Mary Chipman [MSFT]" <mchip@online.microsoft.com> wrote in message
>news:rrqno3pfa964cbrq0c7rf2vnl6dga5br8v@4ax.com...
>>I think you're going to need to provide more information to
>> troubleshoot this. Have you looked at a profiler trace? What are you
>> doing inside of that transaction that it takes 10 minutes? 20,000
>> records is not a lot of data for SQL Server.
>>
>> -Mary
>>
>> On Fri, 11 Jan 2008 18:03:02 +0300, "serg" <serg_NOSPAM_AT_alef.ru>
>> wrote:
>>
>>>I have a long running sql transaction ~10 minutes, ~20000 records
>>>deleted/inserted.
>>>Everything is fine but at the very end when SqlTransaction.Commit() is
>>>perfoming I get a timeout exception.
>>>SqlTransaction does not have a Timeout parameter :(
>>>And I can`t find a way to get an internal SqlCommand to set a
>>>CommandTimeout.
>>>What can I do? Is there any workaround?
>>>Please help
>>>
>>>Divide to many small transactions is not a solution. This should be one
>>>transaction.
>>>
>>>Thansk in adv.
>>>Serg.
>>>
>