Error Message 3910 - Transaction context in use by another session.

Question

Post reply

Error Message 3910 - Transaction context in use by another session.

Eric Peterson

Hall of Fame

Points: 3192
More actions
June 28, 2006 at 8:20 am

#366613

After a 2005 upgrade we get the following errors in our test environment.

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

The application ran without issue using the same procedures and Link Server access on SQL Server 2000

A google search points to several SQL Server 2000 bugs, and a couple of undocumented features in 2005 but nothing on how to get around the problem.

Anyone have any ideas?

Thanks in advance

Eric Peterson

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhushan Default port Points: 1427 More actions · Answer 1

drop and recreate the linked server and try to run the proc again.

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 2

Tried it and it didnt work.

Also tried to set remote transactions

sp_configure 'remote proc trans', 1

and that didnt work either.

Looks like the server is getting confused and dosent allow a potential loopback.

EP

crapz1 Valued Member Points: 65 More actions · Answer 3

Hi, did you get this problem resolved? I'm getting the same error after upgrading to 2005 as well.

Michael Mockus SSC Enthusiast Points: 136 More actions · Answer 4

I am seeing the same issue. I have seen some posts regarding removing the loopback query, but with the way cross-server queries operate, it is often optimal to use loopback. Has anyone found a resolution? It works just fine in 2000 but not in 2005.

sample:

DECLARE

@sql nvarchar(4000)

CREATE

TABLE #name(name sysname)

SET

@sql = 'Select name from '+@@serverName + '.master.dbo.sysobjects'

INSERT

INTO #name

EXEC

RemoteSvr.master.dbo.sp_ExecuteSQL @stmt = @sql

select

* from #name

DROP

TABLE #name

Results:

Msg 3910, Level 16, State 2, Line 1

Transaction context in use by another session.

(0 row(s) affected)

Msg 1206, Level 18, State 118, Line 6

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Kostas Stefanou SSC-Addicted Points: 472 More actions · Answer 5

I have the same problem and I used a workaround like

EXEC (@SQL) AS LOGIN='sa'

and used ##tTable instead. It seems to work.

Has anyone else found something different?

analupe2001 SSC Enthusiast Points: 168 More actions · Answer 6

someone has the solution, I have the same problem 🙁

pls help

analupe2001 SSC Enthusiast Points: 168 More actions · Answer 7

I find this on this link:

http://forums.databasejournal.com/archive/index.php/t-12203.html

Problem:

With autocommit OFF, distributed queries with joins or subselects

between tables on a development server and a linked production server

produce the following error:

Server: Msg 3910, Level 16, State 1, Line 1

Transaction context in use by another session.

The queries run fine when

1. autocommit is turned ON, or

2. autocommit is OFF and the queries reference a copy of the

production database that is made to reside on the same development

server, i.e. the link to the production server is not exercised.

Software configuration:

Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)

Nov 27 1998 22:20:07

Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

Where has Microsoft documented the requirement to set autocommit ON

when executing distributed queries involving linked servers? Or, is

this a known problem? If so, where is it documented?

Thank you in advance for your assistance