This sqlTransaction has completed; it is no longer usable

  • Thread starter Thread starter geeksgk
  • Start date Start date
G

geeksgk

This issue is really frustating because it happens randomnly.

This is the sample layout of my code.

try
{

SQLConnection.Open();
trans = SQLConnection.BeginTransaction();

//Insert the data into tables using datasets

trans.Commit();
}
Catch (SqlException ex)
{
Console.WriteLine(ex.Message);
trans.Rollback();
}

Randomnly I get the error "This sqlTransaction has completed; it is no
longer usable". This error occurs when it tried to execute
"trans.Commit();"

Anyone has any suggestions?

Thanks
 
Does you sql code have any transaction statements, also are you reusing
the same connection object?
 
The sql code does not have any transaction statements (I;m using
datasets and inserting rows into many tables using Dataset.Update
method.

There is only one connection object.
 
This is wierd cause I mimiced the same thing here, and i can't find
this problem can you actaully post more of your code in here
 
It is wierd because it only happens randomnly. What's more weird is the
data got actually inserted into the tables.

I searched in google and some poor souls like me had this problem and
looks like there is no valid reason for this behavior

Here is more detailed code

try
{
SQLConnection.Open();

trans = SQLConnection.BeginTransaction();


IndvlSQLSelectCommand.Transaction = trans;
CompSQLSelectCommand.Transaction = trans;
CompNameSQLSelectCommand.Transaction = trans;
OthrNmSQLSelectCommand.Transaction = trans;

myUtilityClass.WriteLog("Importing Indvl table ...");
IndvlDataAdapter.Update(NewDataSet, "Indvl");

myUtilityClass.WriteLog("Importing Comp table ...");
CompDataAdapter.Update(NewDataSet, "Comp");

myUtilityClass.WriteLog("Importing CompName table ...");
CompNameDataAdapter.Update(NewDataSet, "CompName");

myUtilityClass.WriteLog("Importing OthrNm table ...");
OthrNmDataAdapter.Update(NewDataSet, "OthrNm");


myUtilityClass.WriteLog("Committing Transaction ...");
trans.Commit();
}
catch (SqlException ex)
{
ReturnValue = false;
myUtilityClass.WriteLog("Rollingback Transaction ...");
trans.Rollback();
myUtilityClass.WriteLog("Table Insert Error : " + ex.Message);

}
catch (Exception ex)
{
ReturnValue = false;
myUtilityClass.WriteLog("Rollingback Transaction (Non SQL Error) ...
");
trans.Rollback();
myUtilityClass.WriteLog("Table Insert Error (Non SQL Error) : " +
ex.Message);
}
finally
{

SQLConnection.Close();

IndvlDataAdapter.Dispose() ;
cmdGetIdentity.Dispose() ;
Indvlcmdb.Dispose() ;
indvlPrimaryIdColumn.Dispose();
IndvlForeignIdcolumn.Dispose();

CompDataAdapter.Dispose() ;
Compcmdb.Dispose() ;
CompPrimaryIdColumn.Dispose();

CompNameDataAdapter.Dispose() ;
CompNamecmdb.Dispose() ;
CompNameForeignIdcolumn.Dispose();

OthrNmDataAdapter.Dispose() ;
OthrNmcmdb.Dispose() ;
OthrNmPrimaryIdColumn.Dispose();
OthrNmForeignIdcolumn.Dispose();
OthrNmsForeignIdcolumn.Dispose();

OthrNmNameDataAdapter.Dispose() ;
OthrNmNamecmdb.Dispose() ;
OthrNmNameForeignIdcolumn.Dispose();

}
 
Are you doing any multi threading with this connection and data access
implementation?
 
No multi threading at all. This is a simple and well documented way to
insert data from the datasets into the tables.

I still get the same error randomnly. Got one few minutes back. The
strange thing here is the data actuall got inserted. I don't know why
the heck trans.commit() will throw an error but still sucessfully
commit the records into the tables.
 
Ummm are you getting a SqlException returned, if so it is actually sql
server performing the commit. This one baffles me, I would suggest
openning a ticket with MS, unless someone else like maybe Bill Vaughn
knows.
 
Can you check the @@Trancount value to try to debug where the transaction is
being lost? Something like this:

sqlcommand.CommandText="select @@Trancount";

will return you the transaction level that you are currently executing on.
It should be 0 before beginTransaction, one afterwards, one just before
commit and zero afterwards. Let me know how this works out.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
 
Taking Angels idea you will have to perfrom the @@trancount between
each dataadapter call. It should help pin point he issue
 
This issue is really frustating because it happens randomnly.

This is the sample layout of my code.

try
{

SQLConnection.Open();
trans = SQLConnection.BeginTransaction();

//Insert the data into tables using datasets

trans.Commit();
}
Catch (SqlException ex)
{
Console.WriteLine(ex.Message);
trans.Rollback();
}

Randomnly I get the error "This sqlTransaction has completed; it is no
longer usable". This error occurs when it tried to execute
"trans.Commit();"

Anyone has any suggestions?

Thanks
Click to expand...

I had the same experience on development but not on production. I
noticed that it happened when I was monitoring the tables used by the
transaction through Query Analyzer and MS Access.
HTH
 
Back
Top