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
Solved

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

Posted on 2009-05-14
9
2,400 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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