Linked server does not exist or access denied

Posted on 2004-12-01
Last Modified: 2008-02-01
Windows 2000 pro
SQL Server 2000 sp3a desktop engine

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:

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.
Question by:risoy
    LVL 6

    Expert Comment

    by: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
    on the newly fixed PC.

    Author Comment

    sp_linkedservers run on client:

    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.
    LVL 6

    Expert Comment

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

    ---------           ----------------- ------------ ---------------   ------------------ ------------- --------
    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
    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.


    Author Comment

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

    Author Comment

    I followed this Microsoft KB article:
    "Potential causes of the "SQL Server does not exist or access denied" error message"

    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"

    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.

    Accepted Solution

    Closed, 500 points refunded.

    Community Support Moderator
    Experts Exchange

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    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.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now