Solved

LInked Server Inserts using a trigger SQL Server 2008 to SQL Server 2000 Errors out

Posted on 2009-05-14
9
2,392 Views
Last Modified: 2013-11-10
Hi,

I am trying to have a instead of insert trigger on a table which is in SQL Server 2008, this trigger inserts rows in additional 2 tables (1 in the local server and another in a SQL 2000 linked server).

I when i try to insert this, i get the below error message,
-________________________________________________________________________
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server <Servername> was unable to begin a distributed transaction. OLE DB provider "SQLNCLI10" for linked server <Servername> returned message "The transaction manager has disabled its support for remote/network transactions."
___________________________________________________________________________

On searching the internet i found out that it can be resolved by applying teh cumulative hotfix for SQL Server 2008, i have applied teh patch as mentioned in the article http://support.microsoft.com/kb/954950, but the error still persists.

I also tried to restart the server.

Any help would be appreciated.

0
Comment
Question by:Hitesh_Ramchandani
  • 4
  • 3
  • 2
9 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 125 total points
Comment Utility
Have you enabled MSDTC in both the machines as mentioned below:

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

Make sure that Firewall is turned off in both the machines.
0
 
LVL 25

Expert Comment

by:reb73
Comment Utility
Can you check and ensure that MSDTC (Distributed Transaction Co-Ordinator) service is running in both servers?
0
 
LVL 25

Expert Comment

by:reb73
Comment Utility
rrjegan - > apologies for the nearly duplicate post, had the window open for a while before posting, so didn't get to see your post..
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
reb73,
   Not a problem as this happens to everyone. Even it happened to me many times.
Glad we could help others competitively.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Hitesh_Ramchandani
Comment Utility
I have Distributed Transaction Co-ordinator Service running on both the Servers
One server is on the OS Windows Server 2000 and the other one is Windows Server 2008.

I have not run the above link, because it was meant for Windows server 2003 and i thought it may not be applicable for 2008? Is it applicable for Server 2008 as well? If so i will try it tomorrow when i am back to work.. i'm at home now and the work servers are not accessible.

Appreciate your inputs.. any more guesses?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
The link provided specifies appropriate configuration for MSDTC service in all machines as required for SQL Server.
Hence give it a try.
0
 

Author Comment

by:Hitesh_Ramchandani
Comment Utility
Hi,

I tried going through the above link. But the document does not seem to apply to Windows Server 2008. The point 2, subpoints e and further when trying to enable MSDTC to allow network transaction does not apply to my installation of Windows Server 2008 since those options are not present (see atatched file)........any idea how to get this on Windows 2008

Thanks,
Hitesh


DCom.doc
0
 

Author Comment

by:Hitesh_Ramchandani
Comment Utility
I tried to implement the document on the another SQL Server 2000 db but it does not apply there aas well as the dcomcnfg.exe is different for Windows 2000 Server as well. Pls see attached. Thanks
Dcom-Win2000.doc
0
 

Author Comment

by:Hitesh_Ramchandani
Comment Utility
Thanks Guys,
I got it working by using the steps mentioned in the link http://technet.microsoft.com/en-us/library/cc753620.aspx for Windows 2008.

When a secure option is selected it was giving errors, i have kept the security as "No Authntication" which resolved the issue.

Thanks,
Hitesh
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now