Solved

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

Posted on 2009-05-14
9
2,402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 125 total points
ID: 24385597
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
ID: 24385658
Can you check and ensure that MSDTC (Distributed Transaction Co-Ordinator) service is running in both servers?
0
 
LVL 25

Expert Comment

by:reb73
ID: 24385762
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24385794
reb73,
   Not a problem as this happens to everyone. Even it happened to me many times.
Glad we could help others competitively.
0
 

Author Comment

by:Hitesh_Ramchandani
ID: 24387190
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
ID: 24392642
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
ID: 24393481
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
ID: 24393541
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
ID: 24393997
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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

718 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