?
Solved

Linked server does not exist or access denied

Posted on 2004-12-01
7
Medium Priority
?
1,149 Views
Last Modified: 2008-02-01
Client:
Windows 2000 pro
SQL Server 2000 sp3a desktop engine

Server:
Windows 2000 server std
SQL Server 2000 sp3a server std

The client recently had a hard drive crash and was replaced by a backup-pc - supposed to be 100% identical, but apparently not.

Some tables are being copied from the client to the server, this has stopped working. We get this error message:
    Server: Msg 7391, Level 16, State 1, Line 1
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Some other times we get a time out error. It seems to be related to (distributed) transactions.

Strangely, the below statement succedes running from the client accessing data on the server:
    SELECT TOP 1 * FROM SERVERNAME.TheDB.dbo.tblData

If I run the same query on the server accessing the client server I get this error message:
    Server: Msg 17, Level 16, State 1, Line 1
    SQL Server does not exist or access denied.

Browsing the linked server tables from the client works, but fails from the server.

Any help is greatly appreciated.
0
Comment
Question by:risoy
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12716165
It sounds like a permisions problem on the client PC.  That is the one that had the hard drive crash, there is a setting somewhere where it needs to done on the new hard drive.

Try running
sp_linkedservers
on the newly fixed PC.
0
 

Author Comment

by:risoy
ID: 12716249
sp_linkedservers run on client:

SRV_NAME      SRV_PROVIDERNAME        SRV_PRODUCT      PRVSTRN      S_LOCN      SVR_CAT
-------------------------------------------------------------------------------------------------------------------------------
SERVERNAME      SQLOLEDB      SQL Server      SERVERNAME      NULL      NULL      NULL
CLIENTNAME      SQLOLEDB      SQL Server      CLIENTNAME      NULL      NULL      NULL

CLIENTNAME is of course the client itself.

It is in fact a completely different pc, initially set up in parallell with the original. They are named like CLIENTNAME-1 and CLIENTNAME-2. CLIENTNAME-1 is dead.
0
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12716868
That does not look right, here is what it looks like when I run it here.

SRV_NAME            SRV_PROVIDERNAME  SRV_PRODUCT  SRV_DATASOURCE    SRV_PROVIDERSTRING SRV_LOCATION  SRV_CAT
---------           ----------------- ------------ ---------------   ------------------ ------------- --------
DVHA3P07            SQLOLEDB          SQL Server   DVHA3P07          NULL               NULL          NULL
DVHA2T02\CROSSREF   SQLOLEDB          SQL Server   DVHA2T02\CROSSREF NULL               NULL          NULL
DVHA2T04            SQLOLEDB          SQL Server   DVHA2T04          NULL               NULL          NULL
LPRF2P02            SQLOLEDB          SQL Server   LPRF2P02          NULL               NULL          NULL
repl_distributor    SQLOLEDB          SQL Server   DVHA2T04          NULL               NULL          NULL


Try running
sp_linkedservers
on the server that works correctly.

Then use
sp_addlinkedserver [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

Hit F1 in Enterprise Manager to bring up help for specifics on the command.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:risoy
ID: 12717329
In fact we get the same results. I abbreviated mine, but forgot the datasource one (sorry about that.)
0
 

Author Comment

by:risoy
ID: 12717397
I followed this Microsoft KB article:
"Potential causes of the "SQL Server does not exist or access denied" error message"
 - http://support.microsoft.com/kb/328306

And it solved my problem. Well, in fact way down there it pointed out it could be related to mismatching MDAC versions and pointed to this KB article:
"Component Checker: Diagnose problems and reconfigure MDAC installations"
 - http://support.microsoft.com/kb/307255

I tested and found my client one to be version 2.7. I upgraded to version 2.8 and the problem disappeared.

Thanks for your assistance.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12799491
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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.
Suggested Courses

621 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