Solved

Problem connecting to a linked server, MSDTC not avalible

Posted on 2004-04-13
6
907 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

21 Experts available now in Live!

Get 1:1 Help Now