This SqlTransaction has completed; it is no longer usable.

[joshua drew]

This error happens every once in a while. I cannot figure out what is
wrong or why this happens.

This SqlTransaction has completed; it is no longer usable.

public void InsertVisitorTargeter()
{
MyUtils.SqlClient sqlClient = null;
SqlTransaction tran = null;

try
{
sqlClient = MyUtils.SqlClientFactory.CreateSqlClient();

tran = sqlClient.SqlCommand.Connection.BeginTransaction(IsolationLevel.RepeatableRead,
"VisitorTargeter");
sqlClient.SqlCommand.Transaction = tran;

CommitToDatabase(sqlClient);
tran.Commit();

}
catch(Exception ex)
{
tran.Rollback();
throw(ex);
}
finally
{
// release resources
MyUtil.SqlClientFactory.DisposeSqlClient(sqlClient);
}
}

//edited method for ease of reading a bit.
private void CommitToDatabase(MyUtil.SqlClient sqlClient)
{

System.Diagnostics.Trace.Write("Inside CommitToDataBase",
"Targeter");
System.Diagnostics.Trace.Write("Visitor is " + visitorID,
"Targeter");

sqlClient.SqlCommand.CommandText =
"[dbo].[usp_VisitorTargeter_ins]";
sqlClient.SqlCommand.Parameters.Clear();

// create parameters
sqlClient.SqlCommand.Parameters.Add("@RETURN_VALUE",
SqlDbType.Int,4);

sqlClient.SqlCommand.Parameters.Add("@ID",
SqlDbType.UniqueIdentifier);
sqlClient.SqlCommand.Parameters.Add("@Email", SqlDbType.VarChar,
256);

sqlClient.SqlCommand.Parameters["@Email"].Value = email;

sqlClient.SqlCommand.Parameters["@RETURN_VALUE"].Direction =
ParameterDirection.ReturnValue;
sqlClient.SqlCommand.Parameters["@ID"].Direction =
ParameterDirection.Output;

sqlClient.SqlCommand.ExecuteNonQuery();


// check that sproc ran successfully by returning 1 and affecting
one table row only
if ( 0 != (int)sqlClient.SqlCommand.Parameters["@RETURN_VALUE"].Value)
//||

}

I also have a begin tran and then a rollback/commit tran in the sp.

 

[David Browne]

This error happens every once in a while. I cannot figure out what is
wrong or why this happens.

This SqlTransaction has completed; it is no longer usable.

public void InsertVisitorTargeter()
{ .. . .
I also have a begin tran and then a rollback/commit tran in the sp.

You should never rollback in a stored procedure. Never. Period.

When the rollback in the SP runs it rolls back the WHOLE transaction. Then
when you try to roll back from client code, you get this error.

David

 

[joshua drew]

makes sense.

 

[Chris B]

You should never rollback in a stored procedure. Never. Period.

When the rollback in the SP runs it rolls back the WHOLE transaction. Then
when you try to roll back from client code, you get this error.

David

As I understand you can not nest transactions under adonet. (although you
could under previous ado) Is this in effect what is happening in this case?
Anyway this is very useful information, especially if one is faced with
legacy code full of above such SPs.

Thanks

Chris