Solved

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

Posted on 2014-04-01
3
581 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

808 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