Solved

Problem connecting to a linked server, MSDTC not avalible

Posted on 2004-04-13
6
943 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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