[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4168
  • Last Modified:

Linked server: OLE DB provider does not contain the table

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:

SRV_NAME     SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE
1SQL4             SQLOLEDB                  SQL Server        1SQL4

When I run the following query on 1SQL3:

SELECT * FROM ReadDB..MyP P
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?

Thanks,
Fox
0
foxvision
Asked:
foxvision
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
RiteshShahCommented:
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

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

http://www.sqlhub.com/2009/04/create-linked-server-with-excel-2007.html

however, it is not for MS SQL 2000 but you might get an idea, what to provide while creating linked server.
0
 
RiteshShahCommented:
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.

0
 
foxvisionAuthor Commented:
Thanks Angellll, you are correct.

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

Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now