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?