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

sql stored-procedures sql-server-2008-r2 server-error
White Island picture White Island · Jun 15, 2011 · Viewed 131.7k times · Source

I've been working on this for about a day and a half now, and searched numberous blogs and help articles on the Web. I found several questions on SO related to this error, but I didn't think they quite applied to my situation (or in some cases, unfortunately, I couldn't understand them well enough to implement :P). I'm not sure I can describe this well enough for help... but here goes:

We have a .NET app to track our resources. There's an export function to copy a resource to the time tracking system and the billing system; this accesses a stored procedure that links to the time and billing databases.

I recently moved the billing system database to a new server (original server: Server 2003 SP2, SQL 2005; new server: Server 2008 R2, SQL 2008 R2). I have a Linked Server set up that points to the 2008 databases. I updated the stored procedure to point to the 2008 server, and then I got an error about MSDTC and RPC (http://www.safnet.com/writing/tech/archives/2007/06/server_myserver.html). I enabled 'rpc/rpc out' on the Linked Server and set MSDTC to allow Network Access (something like this: http://www.sqlwebpedia.com/content/msdtc-troubleshooting).

Now I'm getting the above, when I try to run the export function: "This SqlTransaction has completed; it is no longer usable." What seems odd to me is that when I just run the stored procedure (from SSMS), it says it completes successfully.

Has anyone seen this before? Have I missed something in the configuration? I keep going over the same pages, and the only thing I found was that I didn't reboot after making the MSDTC changes (mentioned in here: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/).

I can post part or all of the stored procedure, if it would help... please let me know.

Answer

Phil Sandler picture Phil Sandler · Jun 15, 2011

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?

Related questions

Stored Procedure parameter default value - is this a constant or a variable

Here is my code: USE [xxx] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[problemParam] @StartDate INT = CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))), @EndDate INT = NULL AS BEGIN SSMS is not too happy with …

Read More
Alias names to with rollup in SQL queries?

I am using with rollup in my sql query. I am not getting alias name for rollup. My SQL is SELECT [Column1], sum([Column2]) FROM Tablea GROUP BY [Column2] WITH ROLLUP Which returns s 8 t 8 j 8 null 24 How can I …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More