Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am running 2 queries using the same linked server.

SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust1]
SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust2]

For Query 1 this returns results fine, however query 2 I get the following error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "SERVER" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "SERVER".

The table is accessible on the actual server; has anyone seen this behavior before?

share|improve this question
3  
Can you describe any differences between the two tables? E.g. does one have computed columns, or LOB types, or CLR types, or alias types, are you sure it's not a view, have you tried selecting specific columns instead of *, etc. –  Aaron Bertrand♦ Aug 6 at 11:14
 
This might be a permission issue. How is the linked server defined? –  Sebastian Meine Aug 6 at 13:06
 
OK I have a copy of the table on the same server and can query that using the same linked server, but not my original table. I have checked the securables for the account whose context is being used and that table is not mentioned at all so its not denied. The datatypes are nothing other than int, nvarchar, bit and char. Very confused. One thing however is that this has been set up as Other Data Source linked server referencing another linked server, also set up as Other Data Source but referencing the actual SQL instance. However even calling the 2nd linked server the problem persists. –  Tom Aug 6 at 15:07
 
Further to this I have found a script someone ran which did the following: ALTER TABLE dbo.Customer SET (LOCK_ESCALATION = TABLE). Could this be causing the problem? –  Tom Aug 6 at 15:22
1  
OK I have cracked it, running the query on another SQL 2008 server I got the error: Msg 7325, Level 16, State 1, Line 1 Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object As such I changed to use an OPENQUERY and it now works fine. –  Tom Aug 7 at 10:45
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.