Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-01
3
Medium Priority
?
611 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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