Solved

Problem connecting to a linked server, MSDTC not avalible

Posted on 2004-04-13
6
933 Views
Last Modified: 2008-01-09
Hi experts,

This question has been posted in so many forums yet there are no solid answers. I have 2 sql servers, for sake of this question call them A and B.

On server A, I have created a linked server connection to sever B. If I manage this linked server connection in Enterprise manager, I am able to see the tables in the database that I specified in the connection, which tells me that the linked server has been created correctly.

When I try and execute a query from server A, selecting data on server B I get the error.

Server: Msg 8501, Level 16, State 1, Line 2
MSDTC on server 'My Server Name' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Server A is a standard installation of SQL, where server B is a clustered database running in an active / passive manner. As the two servers are running on seperate domains I have setup a two way trust. Both are running the Win2000 server sp4, with MDAC 2.8.

I have checked and MSDTC is running on both servers, and it is running on the cluster itself.

Can anyone think of a reason why it is still not working?

Thanks
David
0
Comment
Question by:davidnaude
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 10814551
Are you using a fully qualified name for the linked server?

Here is more info on fully qualified names :
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8_qd_12_2.htm
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
ID: 10814574
Ok, a few things to check-out here first of all:

http://support.microsoft.com/default.aspx?kbid=306212
0
 

Author Comment

by:davidnaude
ID: 10814928
mrichmon,

I changed the connection to use a FQDN as opposed to an IP address and I ended up getting an invalid connection error. I made sure that the system was able to resolve the FQDN before hand. Just to test the that the linked server was correctly created, I used the same script to created a linked server and modified it slightly for another server, lets call it server C. Afterward I ran the select statement and I was succesfully able to connect to server C. So would I be right in saying that the linnked server has been defined correctly?

danblake, thanks for that article, it has a few very interesting ideas that I need to check, for example of server B can resolve server A. I will get back to you.

Thanks guys
David
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 35

Expert Comment

by:mrichmon
ID: 10815119
I would tend to agree that it sounds like you are correclty defining the linked server.

I would next check those ideas in danblake's article...
0
 
LVL 13

Expert Comment

by:danblake
ID: 10815370
So would I be right in saying that the linnked server has been defined correctly? Looks that way.
0
 

Author Comment

by:davidnaude
ID: 10842360
Sorry danblake,

I got side tracked and have not had a chance to look much futher at this. As your article has helped so far, I feel it fairve to award you the points.

Cheers
D
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

726 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