Distributed transaction doesn''t work through a Linked Server

Question

Post reply

Distributed transaction doesn''t work through a Linked Server

Joao Salgueiro

Old Hand

Points: 321
More actions
October 6, 2006 at 4:19 am

#153219

Hi,
I'm using two SqlServer 2005 both running on Windows Server 2003 Standard Edition SP1.
When I run a Distributed Transaction I receive the folowwing error:
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.
But I don't have any problem running a simples query using the linked server.
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 ('ABC','X1')
commit transaction
Some one can help me to find the problem?
Thank you in advance
Joao

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

October 6, 2006 at 12:58 pm

#664669

Have you rebooted both ?

* Noel

Joao Salgueiro Old Hand Points: 321 More actions · Answer 2

Hi,

Yes, I have.

Every time I made a change I've rebooted both servers.

João

Joao Salgueiro Old Hand Points: 321 More actions · Answer 3

Hi,

Problem solved!

It was a problem on the network configuration.

The port usar by MSDTC was only open on one direction 🙁

Know it's working,

Thanks,

João

Adriaan Davel Mr or Mrs. 500 Points: 509 More actions · Answer 4

Hi Joao,

Can you please give the Port Number for MSDTC

Joao Salgueiro Old Hand Points: 321 More actions · Answer 5

Hi,

The port was the 4343.

This article helped me tracking the problem:

http://support.microsoft.com/kb/306843

The use of DTCPing is explained.

By

jane.chong SSC Rookie Points: 28 More actions · Answer 6

Hi Joao,

Could you please guide me how to create MSDTC Port? I also facing the same problem.

Thanks.

Joao Salgueiro Old Hand Points: 321 More actions · Answer 7

Hi,

I didn't created the port.

With DTCPing I was able to understand what was the port used by SQL server and check that it was only working on one direction.

The problem was in the firewall.

You need to check on your network if you have firewall between the two SQL server and, if so, check if the port is open for both direction.

Regards

jane.chong SSC Rookie Points: 28 More actions · Answer 8

Hi,

I manage to resolve my problem using TCPPing. TCPPing give result:

++++++++++++DTC Binding Test END+++++++++++++

WARNING:the CID values for both test machines are the same

while this problem won't stop DTCping test, MSDTC will fail for this

So I delete the MSDTC by follow the steps provided from link http://support.microsoft.com/?kbid=873160

After reinstall back the MSDTC, I reboot server then reconfigure again MSDTC finally it works.