1

I am using SQL Server 2008 (let us call this source database server in this question discussion), and in SSMS, I have created a linked server to another SQL Server 2008 database (let us call this destination database server in this question discussion).

When I issue statement -- select * from [linked server name].[database name].[dbo].[table name], error will be returned,

Linked server "ZS" The OLE DB access interface "SQLNCLI10" returned "NON-CLUSTERED and NOT
INTEGRATED "Index" ix_foo_basic_info_nf ", which is incorrect bookmark ordinal 0.

When I issue statement -- select * from openquery([linked server name],'select * from [table name]'), there will be no errors, any ideas what is wrong?

thanks in advance, George

Improve this question

1 Answer 1

Reset to default
1

Is the Index As Access Path option set for the Linked Server Provider? If so, try removing this and see if it fixes the problem.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

"Index As Access Path"? New to me. :-( How to check and why it causes the issue I desribed but no issues for openquery?
This option can be found in Server Objects>Linked Servers>Providers and right click>Properties. It sets whether the the query will use the linked servers indexes to try and retrieve data. Not sure about openquery, but I'm guessing it never uses indexes.
Which ever provider you used when setting up the Linked Server. I think in your case it should be SQLOLEDB.
It depends which one you have set up. Maybe you should look to see what each one is, to find the one you have used.
If you have a linked server, you are using one of the providers.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.