"This SQLtransaction Has Completed; It Is No Longer Usable."... Configuration Error

This SqlTransaction has completed; it is no longer usable.... configuration error?

I believe this error message is due to a "zombie transaction".

Look for possible areas where the transacton is being committed twice (or rolled back twice, or rolled back and committed, etc.). Does the .Net code commit the transaction after the SP has already committed it? Does the .Net code roll it back on encountering an error, then attempt to roll it back again in a catch (or finally) clause?

It's possible an error condition was never being hit on the old server, and thus the faulty "double rollback" code was never hit. Maybe now you have a situation where there is some configuration error on the new server, and now the faulty code is getting hit via exception handling.

Can you debug into the error code? Do you have a stack trace?

This SqlTransaction has completed; it is no longer usable. - Cannot figure out what is wrong

throw new System.Net.WebException("Error Creating License");

You are throwing an exception, and then trying to rollback a transaction that you already committed. This can't work. Once you have committed it, you can't roll it back.

I'd suggest one of two approaches.

Approach One:

Change your logic to create the licence key up front then do a single INSERT to the database. This will be faster, hold very few DB locks (and for a short window of time) and not require any use of explicit transactions (since you will be doing a single insert). The code will likely be substantially simpler.

Approach Two:

Remove all use of transactions. Insert the record (basically how you do now). Then, without keeping a DB connection / transaction open, create the licence. If the licence was created successfully, then run a DB update (of the record you just inserted). If the licence was not created successfully, then run a DB delete (of the record you just inserted).

transaction rollback error (This SqlTransaction has completed; it is no longer usable)

When you exit the USING block

Using connection As New SqlConnection...
End Using

Your connection is closed and disposed and any pending transactions against that connect are committed. You cannot have the same transaction against multiple connections.

Only create and open your connection once. Pass this connection (and transaction) to your method that is going to process your SQL. Then when it is all done, you can commit your transaction and close/dispose your connection.

Dealing with a single connection across multiple SQL commands will increase the speed of your program because there is overhead involved with each connection that can slow you down.

---

Related Topics