Solved

Problem connecting to a linked server, MSDTC not avalible

Posted on 2004-04-13
6
917 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 42
sql help 8 55
Slow Connectivity over ODBC 8 32
Query to capture 5 and 9 digit zip code? 4 20
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

12 Experts available now in Live!

Get 1:1 Help Now