Error Message 3910 - Transaction context in use by another session.
-
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
-
drop and recreate the linked server and try to run the proc again.
-
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
-
Hi, did you get this problem resolved? I'm getting the same error after upgrading to 2005 as well.
-
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.
-
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?
-
someone has the solution, I have the same problem
pls help
-
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
Copyright (c) 1988-1998 Microsoft Corporation
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply