Linked server: OLE DB provider does not contain the table

Posted on 2009-04-20
Last Modified: 2012-06-27
I have 2 MS SQL 2000 servers, one 1SQL3 and the other 1SQL4, and I need to query some data from a table in 1SQL4 and update to a table in 1SQL3, so I use linked server.

When I use sp_linkedservers to view the linked server on 1SQL3, this is what I get:

1SQL4             SQLOLEDB                  SQL Server        1SQL4

When I run the following query on 1SQL3:

JOIN [1SQL4].MyInt.dbo.MySource S  ON (P.Title = S.Name)

I get an error:

OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='1SQL4', TableName='"MyInt"."dbo"."MySource"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider '1SQL4' does not contain table '"MyInt"."dbo"."MySource"'.  The table either does not exist or the current user does not have permissions on that table.

I use sp_tables_ex '1SQL4' and noticed MySource does not exist in Table_Cat, only master table.

What is missing here?

Question by:foxvision
    LVL 142

    Accepted Solution

    the issue is usually the linked server security setup.
    if the GUI (enterprise manager), when you open the properties of the linked server, you have the tab security.
    ensure that you specify a valid sql login there that has the permissions to the database/table you want to query over that linked server.
    LVL 31

    Expert Comment

    can you please paste the code which you have used to make linked server? I guess you are missing something while creating it.

    BTW, have a look at following URLs for linked server

    however, it is not for MS SQL 2000 but you might get an idea, what to provide while creating linked server.
    LVL 31

    Expert Comment

    I also have exactly same doubts, AngelIII has, that is why I asked for the code, if you are using script, you can use

    EXEC sp_addlinkedsrvlogin

    to add credential of linked server.


    Author Closing Comment

    Thanks Angellll, you are correct.

    I changed the login in the security tab and it works.


    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

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    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.

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now