"Dave Sexton" wrote:

> Hi,
>
> <snip>
>
> > The problem is that my ADO.NET command is given a timeout period of 2
> > minutes and it will occasionally timeout.
>
> Why can't you just increase the SqlCommand's timeout?
>
> > This causes an OPEN TRANSACTION in
> > the database (i.e. using DBCC OPENTRAN shows an open transaction) for
> > several
> > minutes, which locks my table and causes other timeouts.
>
> Are you closing the connection after the SqlCommand throws its exception?
>
> > I am thinking of just removing the transaction handling from the stored
> > procedure and just wrapping the stored procedure call into an ADO.NET
> > transaction.  I believe this will help resolve my OPEN TRANSACTION issue in
> > the case of a timeout, but would moving the transaction to an ADO.NET
> > transaction reduce performance of my stored procedure call?
>
> SqlTransaction just sends BEGIN TRANS like you were doing in your procedure.
>
> SqlConnection.BeginTransaction method on MSDN:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassBeginTransactionTopic1.asp?frame=true
>
> If your SqlCommand times out you should close the connection and all pending
> transactions will be rolled back.
>
> SqlConnection.Close method on MSDN:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassCloseTopic.asp?frame=true
>
> --
> Dave Sexton
>
>
>

> Hello,
>
> I have an application that is currently using a T-SQL stored
> procedure that uses a transaction to perform an update to a single
> record and then INSERT, what could be HUNDREDS OF THOUSANDS of
> records, into a related table.  My T-SQL looks like this:
>
> BEGIN TRANS
>
> UPDATE MyTable SET statusCode = 2 WHERE MyTable.myTableId = @myTableId
> IF @@ERROR <> 0
> BEGIN
>  /*code to rollback*/
> END
>
> /*could insert hundreds of thousands of records (i.e. 300,000)*/
> INSERT INTO MyRelatedHistoryTable (Col1, Col2) SELECT ColOne, ColeTwo
> FROM MyRelatedTable (NOLOCK)
>
> IF @@ERROR <> 0
> BEGIN
>   /*ROLLBACK code here*/
> END
>
> COMMIT TRANS
>
>
> The problem is that my ADO.NET command is given a timeout period of 2
> minutes and it will occasionally timeout.  This causes an OPEN
> TRANSACTION in the database (i.e. using DBCC OPENTRAN shows an open
> transaction) for several minutes, which locks my table and causes
> other timeouts.
>
> I am thinking of just removing the transaction handling from the
> stored procedure and just wrapping the stored procedure call into an
> ADO.NET transaction.  I believe this will help resolve my OPEN
> TRANSACTION issue in the case of a timeout, but would moving the
> transaction to an ADO.NET transaction reduce performance of my stored
> procedure call?