This SqlTransaction has completed it is no longer usable configuration error

Question

I've been working on this for about a day and a half, and I've looked through a lot of blogs and online how-to guides. On SO, I came across a number of queries pertaining to this mistake, but I didn't think they were exactly applicable to my circumstances (or, sadly, in some cases, I couldn't grasp them clearly enough to implement:P). I'm not sure if I can explain this clearly enough to be of assistance, but I'll try:

To track our resources, we have a.NET application. A stored procedure that connects to the time and billing databases is accessed by the export function, which copies a resource to the time tracking system and the billing system.

The billing system database was just transferred to a new server by me (original server: Server 2003 SP2, SQL 2005; new server: Server 2008 R2, SQL 2008 R2). I've set up a Linked Server that directs traffic to the 2008 databases. A message concerning MSDTC and RPC appeared after I altered the stored procedure to refer to the 2008 server. I set MSDTC to allow Network Access and enabled "rpc/rpc out" on the Linked Server (similar to this: http://www.sqlwebpedia.com/content/msdtc-troubleshooting).

When I try to use the export function, I now get the following message: "This SqlTransaction has completed; it is no longer useable." What puzzles me is that when I simply run the stored procedure (via SSMS), it reports a successful completion.

Has anyone ever witnessed this? Has the configuration been configured incorrectly? The only thing I discovered after repeatedly reading the same pages is that I didn't reboot after making the MSDTC adjustments (discussed here: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/).

If posting the entire stored method will be helpful, please let me know.

Answer 1

This error message, in my opinion, is the result of a "zombie transaction."

Search for any potential locations where the act is being committed twice (or rolled back twice, or rolled back and committed, etc.). Does the transaction get committed by the.Net code after the SP has already done so? Does the.Net code attempt to roll it back again in a catch (or finally) clause after hitting an error?

On the old server, it's likely that an error condition was never met, preventing the erroneous "double rollback" code from ever being executed. Perhaps you are now in a situation where the new server has a configuration mistake, and the flawed code is being hit by exception handling.

I hope this helps you.