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:

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?

Who is Participating?
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.
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.
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.

foxvisionAuthor Commented:
Thanks Angellll, you are correct.

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

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.

All Courses

From novice to tech pro — start learning today.