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.
risoyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
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
risoyAuthor Commented:
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
Duane LawrenceCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

risoyAuthor Commented:
In fact we get the same results. I abbreviated mine, but forgot the datasource one (sorry about that.)
0
risoyAuthor Commented:
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
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.