asked on

Linked server does not exist or access denied

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.

Duane Lawrence

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.

risoy

ASKER

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.

Duane Lawrence

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.

risoy

ASKER

In fact we get the same results. I abbreviated mine, but forgot the datasource one (sorry about that.)

risoy

ASKER

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.

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial