Solved

Table Does Not Exist Linked Server 2000 to 2005

Posted on 2014-03-23
6
785 Views
Last Modified: 2014-04-01
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.
0
Comment
Question by:andersonpower
  • 3
  • 2
6 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39949830
with SA login it will indeed be difficult to run into permission issues.
so, the other things I can think of are:
* using [ ] instead of  " " might help
* case sensitivity issues, check the collation of the 2 servers/databases etc ...
* typos  (like I <> l  <> 1 ) spaces etc ...
* you are NOT connected to the right server or instance
0
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 39957265
"2000 is linked successfully but can only query data from Master database, not other databases"

So you run a query from SQL 2000 against SQL 2005?
Did you created a Linked server on SQL 2000 TO SQL 2005 and one linked Server on SQL 2005 TO SQL 2000?

Is not quite clear from "2005 is linked successfully to 2000 and can query from various databases on it.  2000 is linked successfully ..." to what?


Also, in your direct queries you must use FULL (four) name qualifier like:

 LinkedServerNameHere.IERP86US.dbo.Item

right?
0
 

Author Comment

by:andersonpower
ID: 39958929
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].
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:andersonpower
ID: 39969555
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.].
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39969624
"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.
0
 

Author Comment

by:andersonpower
ID: 39969977
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
.htaccess file settings 4 36
Calculating Business Hours 19 65
BULK INSERT most recent CSV 19 20
SQL Date Retrival 7 28
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

758 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

22 Experts available now in Live!

Get 1:1 Help Now