tags:

views:

33

answers:

1
Q: 

openquery issue in SQL Server

Hello everyone,

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

+1  A: 

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.

starskythehutch  2010-06-17 12:50:50
"Index As Access Path"? New to me. :-(How to check and why it causes the issue I desribed but no issues for openquery?
George2  2010-06-17 12:54:48
From this link, I find what means Index As Access Path, but can not find how to turn it off.http://msdn.microsoft.com/en-us/library/ms188095.aspx
George2  2010-06-17 13:03:55
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.
starskythehutch  2010-06-17 13:05:51
You mean the first form of SQL uses index, and openquery not using index? But why using index will cause issues?
George2  2010-06-17 13:15:22
There are many providers listed under Providers, like ADsDSOObject, MSOLAP, SQLNCLI, SQLNCLI10, SQLOLEDB, which one should I remove Index As Access Path properties?
George2  2010-06-17 13:22:46
Which ever provider you used when setting up the Linked Server. I think in your case it should be SQLOLEDB.
starskythehutch  2010-06-17 13:29:04
From where you see SQLOLEDB? I saw SQLNCLI10. :-)
George2  2010-06-17 13:31:06
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.
starskythehutch  2010-06-17 14:42:00
Actually I do not setup any provider explicitly. I just write SQL. Any ideas how to find which provider I am using?
George2  2010-06-17 15:15:22
If you have a linked server, you are using one of the providers.
starskythehutch  2010-06-17 16:46:11
I mean how could I quickly find which one I am using? I think to set every provider to disable "Index As Access Path" seems stupid. :-)
George2  2010-06-17 16:50:16
Thanks, question answered!
George2  2010-06-18 02:02:28

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type