Exception: The SqlTransaction has completed; it is no longer usabl

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I hv encountered this exception 'The SqlTransaction has completed; it is no
longer usable.' in my VB application. The application calls a T-SQL stored
procedure, returning around 9000 rows in a DataSet and back to the app. It is
built using VB .Net 2003, with SQL Server 2003 as the database.

When that exception is thrown, I tried running the same stored procedure in
Query Analyzer and it returns results within 4 seconds. And the weird thing
is, after that error occured, even if I don't do anything to the system,
after a few minutes it mysteriously goes away. And the VB app can return data
within a few seconds! It is happening every now and then, and can occur even
if only one or two users using the system.

I was thinking if Query Analyzer can return data very quickly... Then cld it
be something to do with the ADO.

Hope someone can help me with this mysterious happening. TQ very much in
advance.
 
We can't give you a useful response without seeing the relevant code.

That exception will typically occur if you try to commit or rollback a
transaction that was already committed or rolled back.
 
Hi all,

TQ for ur replies. Below are the sample code:


Public Function FetchMyReport(ByVal vdtLastUpdateOn As System.DateTime)
As System.Data.DataSet
Dim conn As SqlClient.SqlConnection
Dim tran As SqlClient.SqlTransaction
Dim dsReport As New Data.DataSet

conn = Me.GetConnection
conn.Open()
tran = conn.BeginTransaction
Try
dsReport = Me.FetchMyReport(vdtLastUpdateOn, conn, tran)
tran.Commit()
Return dsReport
Catch ex As Exception
tran.Rollback()
Throw ex
Finally
conn.Close()
End Try
End Function

Public Function FetchMyReport(ByVal vdtLastUpdateOn As System.DateTime,
ByVal conn As Data.SqlClient.SqlConnection, ByVal tran As
Data.SqlClient.SqlTransaction) As System.Data.DataSet
Dim dsReport As Data.DataSet
Dim cmd As SqlClient.SqlCommand
Dim daFillData As SqlClient.SqlDataAdapter

dsReport = New DataSet
cmd = New SqlClient.SqlCommand("MyStoredProcedure")
cmd.CommandTimeout = 15
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@LastUpdateOn", vdtLastUpdateOn)
cmd.Connection = conn
cmd.Transaction = tran
daFillData = New Data.SqlClient.SqlDataAdapter(cmd)
daFillData.Fill(dsReport, "Table")
Return dsReport
End Function


"MyStoredProcedure" is a select statement with joins stored procedure.
However all tables involved have a WITH (NOLOCK) hint. Therefore there is no
timeout. I even tried by extending the CommandTimeout = 120, but it still
fails.

I was thinking cld it be the 9000+ returned rows. However that still does
not explain why sometimes it works very well, and sometimes it fails
completely, only to work again after more than 20 minutes. Cld it also be a
network problem?

My production system is facing very weird problems. Sometimes it becomes
spastic, timeout all the time, even for only 1 user. And after 2 - 3 hours,
it works very well again, able to handle many users. I hv posted this problem
on 05-Oct at sqlserver.programming but has not received any positive replies.

Cld anyone pls kindly share some light on this weird problem? TQ very much
in advance.
 
I got the same error and it was due to executing a method inside the try catch which itself was producting and error. Once I added a second try/catch for it, I solved the problem.

Hope this works.
Carlos

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
Hi,

Thx for ur reply.

Cld u pls kindly elaborate with a sample what do u mean by 2nd try/catch? Is
it a 2nd try/catch within the first? Do I need to call the DataAdapter.Fill
method again, and where?
 
Back
Top