December 5, 2005 at 3:55 am
I'm getting a strange error trying to run a distributed transaction between 2
SQL 2005 machines.
The error reads
'OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message
"No transaction is active"'
'Msg 7391, Level 16, State 2, Line 2'
'The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "RemoteServer" was unable to begin a distributed transaction"
The query that I'm trying to run is very simple. The table A exists on both
machines
BEGIN TRANSACTION
INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')
INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Remote')
COMMIT TRANSACTION
DTC is running on both machines and I've configured it to allow network
access, remote clients, remote administration, Inbound and Outbound
communications (Component Services->My Computer->DTC)
The linked server uses a specific security context and allows data access,
rpc and rpc out.
Both the inserts run fine by themselves. It's only when run together in a
transaction that they break.
Has anyone got a solution, or suggestion please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2005 at 11:06 am
Not sure about 2005, but I had two 2003 servers in a workgroup and there was some registry entry I had to make to allow DTC to function across them. I had a different error, something about unable to begin a distributed transaction.
It was similar to this: http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql, but I know I made a registry change.
December 5, 2005 at 2:03 pm
The registry change Steve mentioned is to disable security for RPC. If MSDTC is configured properly, you do not need to do this. But sometimes it just does not work regarless how you configure the MSDTC. Then this registry change is the last straw.
Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC, add dword value key TurnOffRpcSecurity. Assign its value to 1.
Another thing is for distributed transactions in SQL server, you must turn on XACT_ABORT and ANSI_NULLS
SET XACT_ABORT ON (run time)
SET ANSI_NULLS ON (parse time)
December 5, 2005 at 11:34 pm
Turning off the rpc security didn't help. I was getting the error about not been able to enlist in the specified transaction coordinator until I made some changes in the DTC setup.
I tried XACT_ABORT and ANSI NULL and neither helped.
I googled this and didn't find anything useful. Think I'm ging to escalate it to PSS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2006 at 6:57 pm
I am experiencing this as well referencing a stored procedure on a linked server from a trigger. Unfortunately, the suggestions here didn't help me either. Were you ever able to resolve the issue?
January 5, 2006 at 7:04 pm
Is MS DTC configured to run under Network Service on both servers?
K. Brian Kelley
@kbriankelley
January 5, 2006 at 7:25 pm
Actually, I found that in the MSDTC security configuration dialog from the component services, if I set the "Transaction Manager Communication" authentication from "Mutual Authentication Required" to "Incoming Caller Authentication Required", it now works!
In my case I have a W2K3 server with SQL2005 trying to called a proc on a W2K SQL2000 machine from within a trigger. I had been test the linked server for a while and all was working fine until I tried calling an SP from within a SQL 2005 trigger.
I think this solves my issue. Thanks for the quick response though.
-Chad
September 29, 2006 at 4:09 am
Hi,
Same problem here!
I'm using two SqlServer 2005 both running on Windows Server 2003 Standard Edition SP1.
Both doesn't belongs to a Domain. They are configured in they own workgroup.
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
MSDTC version 5.2.3790.1830
On both servers:
- Service "Remote Procedure Call" is running using the account "NT AUTHORITY\NetworkService";
- On Component Services -> Computer -> My Computer Properties -> tab MSDTC -> Security Configuration all box are checked. On the Transaction Manager Communication the chosen is "No Authentication is Required".
- On Local Computer Policy -> Administrative Templates -> System -> Remote Procedure Call the "Restriction for Unauthenticated RPC clients" is enabled and the restriction to apply is "None".
- Windows Firewall is disabled
- Registry key is set to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity is set to "1"
Schema on server [myServer2]:
USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[myTable](
[varchar](5) NOT NULL,
[name] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SQLStatement run on server [myServer1]
begin distributed transaction
insert into [myServer2].myDB.dbo.myTable values ('ACD','LX')
commit transaction
The error message:
OLE DB provider "SQLNCLI" for linked server "myServer2" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "myServer2" was unable to begin a distributed transaction.
Some one can help me find the problem?
Thank you in advance
Joao
May 30, 2007 at 1:21 am
Thanks...that worked for me too!
January 18, 2008 at 8:16 am
It could relate to firewall
April 8, 2009 at 4:55 pm
Chad Michael (1/5/2006)
In my case I have a W2K3 server with SQL2005 trying to called a proc on a W2K SQL2000 machine from within a trigger. I had been test the linked server for a while and all was working fine until I tried calling an SP from within a SQL 2005 trigger.
-Chad
I have a similar problem. But setting the MS DTC like that did not help.
September 4, 2009 at 12:52 am
Yesterday I spent half a day looking for the solution of this problem. In my case this one helped: http://www.eggheadcafe.com/conversation.aspx?messageid=34113657&threadid=34091450
This is for the case when You have two machines in separate vlans and different domains.
And here is the solution:
"After adding on each SQL-Server the opposite in c:\windows\system32\driver\etc\hosts
connection can be established and remote update works."
April 28, 2010 at 2:39 pm
I have the same exact issue as mentioned by Joao Salgueiro...can anyone suggest a quick fix...i am sure its a small tweak but not able to figure out the issue.
Another question: Will it be a problem to run linked server between a 32bit and 64 bit sql server. Because regular inserts/deletes work fine but when run within a transaction they fail:
OLE DB provider "SQLNCLI" for linked server "ext03_Stage1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure CCC_List_ITRIG, Line 16
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ABC03_Stage1" was unable to begin a distributed transaction.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply