Solved

Executing Queries from SQL 2000 to SQL 2005 Linked Server Throws Error on Indexed Tables

Posted on 2014-04-01
3
573 Views
Last Modified: 2014-04-01
I have a SQL 2005 R2 server that I want to link to from a SQL 2000 server running SP4.  I can create the linked server okay, and I can query any table in the database that does not have an index.  For the tables that do, however, (most of them) I get the following error when I try to query data from it:

SELECT * FROM LinkedServerName.DatabaseName.dbo.Item

Server: Msg 7319, Level 16, State 1, Line 1
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
Comment
Question by:andersonpower
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39969882
I don't have a SQL 2000 handy to try and see what are the options on the SQL 2000  SQLOLEDB provider but could you please check to see if there is a "allow in process" available and try check it if it is not checked. You find providers via SSMS->Server Objects-> and listed under LinkedServers.

If that does not work...upward compatibility may not be possible to be achieved like that and you still should be able to link SQL 2000 to SQL 2005 and run queries against 2005 from 2000 but by using ODBC SystemDSN not OLEDB. You can create a new linked server using that ODBC connection via "Microsoft OLEDB for ODBC drivers" (select from dropdown list of available providers)

Also...please check the service packs on both to at least make sure you run SQL 2005 SP4 and I believe SQL 2000 SP4 was last one over there as well.
0
 

Author Comment

by:andersonpower
ID: 39969945
Yes, both are running SP4.  I've tried using the SQL connection and using ODBC, I just end up with a different error when I run it through ODBC.
0
 

Author Comment

by:andersonpower
ID: 39969979
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

18 Experts available now in Live!

Get 1:1 Help Now