Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-05-14
9
Medium Priority
?
2,404 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 250 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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