how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].