MSDTC and Linked Servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

MSDTC and Linked Servers

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 15:28:58

I have setup a trigger to update records on a linked server, the link works fine and I can query tables on the remote server. However when my trigger activates I get the following error

The operation could not be performed because OLE DB provider SQLNCLI for linked server "NHS" was unable to begin a distributed transaction.

OLE DB provider SQLNCLI for linked server "NHS" returned message
"No transaction is active"

I've followed a microsoft KB article and installed MSDTC on both servers, what else do I need to do?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-26 : 23:03:04

Are they running? Did you enable network access in dtc?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-27 : 04:18:30

Yes enabled on both servers, I can telnet from server to server on the DTC port 3372. What else should I try to diagnose the issue?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 04:31:07

first, i would suggest that you don't do this with a trigger. You should look at transactional replication instead.

however, we can also try to help with your dtc problem although I think it is a bad idea and bad design. To troubleshoot you will need to make sure that network dtc is active on both systems. you can add that through add/remove programs. try following the steps in this KB article http://support.microsoft.com/kb/817064

Do those steps and let us know what happens. please post the exact error messages you get.

also, is there a firewall or router the filters tcp/udp ports between the two systems?

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-27 : 06:37:39

Hi,

Okay followed the article to the letter on both servers, no error messages with the actual DTC installation, just the error when I try and delete a record on server 1

The operation could not be performed because OLE DB provider SQLNCLI for linked server "NHS" was unable to begin a distributed transaction.

OLE DB provider SQLNCLI for linked server "NHS" returned message
"No transaction is active"

The servers are both at our ISP, they assure me they are not blocking port 3372. I do know however that they have the netbios ports blocked, I think its 139 - does DTC use this port as well as the 3372?

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 14:51:06

DTC uses TCP port 135 and random TCP ports between 1024-65534 for RPC communication. This makes it fun to configure in a firewalled environment.

Before we get into dealing with the ports, can you check that this isn't an issue with the DTC security configuration. btw, Are you using DTC between 2 win2k3 hosts or are you using win2k as well?

Here is a KB article about new features for DTC that came with win2k3 Sp1 that can cause problems (specifically, a new authentication requirement) and how to make it behave as it did pre-sp1: http://support.microsoft.com/kb/899191/

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-27 : 16:11:58

Hi ec,

Thanks for your help.

I've run through that article and DTC seems to be configured correctly on both servers, both servers are win2k3 sp2.

I've confirmed with ISP that ports 135 and 139 are blocked.

Is there any other option for me? Can the port number be modified? Or did MS not consider that ISP's were likely to block these ports routinely?

Thanks again.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 16:31:41

if port 135 is blocked you are out of luck. Your best bet is to open a ticket with microsoft on this to see if they can help further.

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-27 : 17:23:52

Cheers, where can I do that?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 19:48:24

quote:
Originally posted by Mondeo

Cheers, where can I do that?

go to microsofts website, click on support and you should see how to call them.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 19:51:02

again, my advice is to try using transactional replication instead of a trigger. Especially given the setup you are having to work with.

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-28 : 08:08:34

Transactional replication, will this work without needing that port open?

Also the servers are both 2005 express edition, will it still work?

Cheers

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-28 : 11:50:26

quote:
Originally posted by Mondeo

Transactional replication, will this work without needing that port open?

Also the servers are both 2005 express edition, will it still work?

Cheers

you can't publish replication articles using express edition, so unfortunately you are out of luck as far as replication goes.

Your next best option is to schedule a batch job to update the remote database. Since sql2k5 express edition doesn't have sql agent, you will need to use the OS task scheduler to do this. You could run a batch job every minute to keep the two systems somewhat in sync.

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-01 : 08:21:35

Hi ec,

The plot thickens...

I've been able to request the ISP puts these two servers in a VLAN on thier switch, meaning that traffic between they bypasses the firewall and no ports are blocked.

BUT...

The issue remains...

What else should I try to try and diagnose this?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-01 : 22:22:50

Enabled msdtc traffic on switch?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-02 : 05:03:44

quote:
Originally posted by rmiao

Enabled msdtc traffic on switch?

Hi,

Don't follow? All ports are open within the VLAN on the switch.

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-02 : 12:51:29

are you running winxp or a server OS?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-02 : 12:56:00

Both servers are Windows 2003 Server SP2

Cheers

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-02 : 13:04:24

have you tried any dtc testing tools? microsoft has 3 or 4 of them. here is a link to dtctester http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293799

there is also dtcping and a few others.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-02 : 13:11:08

here is another KB article to check out. it is specific to the 7391 error you are getting. http://support.microsoft.com/kb/839279/en-us

try to see if you can reproduce the dtc error using the sample code at the bottom of the page as a test. This would eliminate your trigger code as being the problem.

-ec

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-02 : 16:18:11

The output from the DTC tester is below. It gives 4 possible options for the error.

1. Ports closed on firewall - I can telnet between the machines on port 135
2. Bad WINS/DNS entries - not sure how this is relevent, I dont seem to be experiencing any name resolution issues.
3. Misconfigured network - Routing between the machines seems fine, they are on different subnets but they can communicate fine, ping works etc.
4.Misconfigured SQL Server machine that has multiple netcards - this caught my eye. Both these servers have two netcards and 2 IP's, the IP's are both consecutive public IP's in the same subnet.

Any clue eyechart?

Cheers

C:\dtc>dtctester dtc sa mypassword
Executed: dtctester
DSN: dtc
User Name: sa
Password: mypassword
tablename= #dtc20415
Creating Temp Table for Testing: #dtc20415
Warning: No Columns in Result Set From Executing: 'create table #dtc20415 (ival
int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=0,msg='[Microsoft][SQL Native Client]The transaction
has already been implicitly or explicitly committed or aborted
'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Native Client]Invalid cursor s
tate
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.

C:\dtc>

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources