Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.