asked on

Table Does Not Exist Linked Server 2000 to 2005

I have two servers, a SQL 2000 and a SQL 2005 and need to have them be linked to each other. 2005 is linked successfully to 2000 and can query from various databases on it. 2000 is linked successfully but can only query data from Master database, not other databases. If I try and query from another database I get the following:

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

Both servers are using sa logon and have same settings.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andersonpower

ASKER

Sorry, yes, both servers have a link to each other.

I can query using the full name from 2005 server to 2000 server database tables successfully.

I can ONLY query tables in the master database going from the 2000 to 2005 server.

But this 2000 server is connect to a DIFFERENT 2005 server and can query tables from that server's database fine. That one is using a MediaCatalogDB OLE DB Provider setup.

If I try to recreate link to the 2005 server I need to connect to using MediaCatalogDB OLD DB Provider instead of Microsoft OLE DB Provider for SQL Server I get the following error message:

Could not initialize data source object of OLE DB provider 'MediaCatalogDB.11'.
OLE DB error trace [OLE/DB Provider 'MediaCatalogDB.11' IDBInitialize::Initialize returned 0x80030003].

andersonpower

ASKER

I recreated the linked server using my SQL 2008 management tools and now if I can query some tables if they don't have indexes, but the tables that DO have indexes return the following error:

OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_IMA_CustItemID' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

lcohan

"I recreated the linked server using my SQL 2008 management tools"....linked server from where to where and what provider was it used? That's where you should find the answer to your question - some providers can be configured or you can add specifics to the connection string - just try script it from SQL SSMS and see what I'm talking about.

andersonpower

ASKER

I had to go into the SQLOLEDB provider and remove the "Index as access path". It just took me a while to figure out where to go to update the providers on the SQL 2000 server.