[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem connecting to a linked server, MSDTC not avalible

Posted on 2004-04-13
6
Medium Priority
?
947 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 2000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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