Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

803 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