You can set the ConnetionTime out value to 30 instead of 15 (default). This you can pass it in the connection string. The Commit is taking more than 15 seconds and hence the issue.
serg wrote:
Yes, i know about clr in sql2005But now this is not my willing or unwilling to
24-Jan-08
Yes, i know about clr in sql200
But now this is not my willing or unwilling to rewrite it
It is money question. No one want to give money to rewrite app that is
working quite well
There should be much more weighty reasons to do it
I should wait for users maturing. :
Thanks
Serg
Previous Posts In This Thread:
timeout exception during SqlTransaction.Commit() any workaround please help?
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 hel
Divide to many small transactions is not a solution. This should be one
transaction
Thansk in adv
Serg.
I think you're going to need to provide more information totroubleshoot this.
I think you're going to need to provide more information t
troubleshoot this. Have you looked at a profiler trace? What are yo
doing inside of that transaction that it takes 10 minutes? 20,00
records is not a lot of data for SQL Server
-Mar
Hi, MaryIt is an accounting procedure that is closin finacial year.
Hi, Mar
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 snippe
public class MyConnMgmtClass : IDisposabl
private IDbTransaction _transaction
public void Commit (
if (_transaction == null
throw new AlefException (00013, null)
_transaction.Commit(); // at this string occurs timeout
exceptio
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 procedure
...
command.CommandTimeout = 180; //second
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 lo
Serg
Re: timeout exception during SqlTransaction.Commit() any workaround please help?
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 store
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
Actually under interfaces i am using native SqlClient.
Actually under interfaces i am using native SqlClient. Initialy it was OLEDB
but with migrating to net 2.0 it became SqlClient.
As to single stored procedure.
There are complicated math calculations transact-sql is not well for them.
Serg.
T-SQL isn't good at math, but the CLR is.
T-SQL isn't good at math, but the CLR is. You can write your sprocs in
managed code in SQLS 2005, which would give you the best of both
worlds. For more info, see
http://msdn2.microsoft.com/en-us/library/ms345136.aspx.
--Mary
Yes, i know about clr in sql2005But now this is not my willing or unwilling to
Yes, i know about clr in sql2005
But now this is not my willing or unwilling to rewrite it.
It is money question. No one want to give money to rewrite app that is
working quite well.
There should be much more weighty reasons to do it.
I should wait for users maturing.
Thanks.
Serg.
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 4
http://www.eggheadcafe.com/tutorial...45-8b37cb7f3186/wpf-report-engine-part-4.aspx
