Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

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

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
andersonpower
Asked:
andersonpower
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
andersonpowerAuthor Commented:
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
 
andersonpowerAuthor Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now