question

paulunsy avatar image
paulunsy asked

ODBC Linked Server OPENQUERY Hangs

Hi Guys I have a bit of a problem. My linked server test returns successful, but it just hangs when I try and run: select * from openquery(BRADFORD,'select top 1 id from ttfa0s') I can run the same query, or anything I want in MS Access using the same ODBC connection and it returns good and quick so I'm quite confused... Can anyone see what I'm doing wrong? Thanks in advance... UPDATE: I've tinkered about a little, and set up another test linked server, but now I'm getting: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "TEST" reported an error. The provider reported an unexpected catastrophic failure. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "TEST". even though the EXACT same query works in Access using the EXACT same ODBC DSN. I'm using the "Microsoft OLE DB Provider for ODBC Drivers" provider in SQL2012. Please help... It's making a monkey out of me.... **Another UPDATE:** Ok.. I've been away for a while, but now I have had a little progress. I have found that I can query some tables fine, but not others. When I get the "unexpected catastrophic failure" I get the field headers returned, but no records? For other other tables, it quite happily returns data with a slight performance lag, but nothing I can't live with. Anybody have any ideas? Thanks
linked server
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

paulunsy avatar image paulunsy commented ·
Yip. Oddly enough, the only security on the Advantage database is the access to the folder that the table files live in... That's controlled by AD which the majority of the company has access to, including me... This is something that I'm intending to change. The lack of security is scary, but I need to learn more about Advantage DB first, and get to grips with this new company...
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Has your SQL Server service user got the right rights to access the Access database?
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Some things to try: 1. disabling Pre-Fetch for the connection. In your ODBC declaration, add the following: PREFETCH=0 2. Configure the OLE DB provider to run in-process rather than out-of-process? See [here][1]. [1]: http://msdn.microsoft.com/en-us/library/ff772782.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
Run the SQL Server profiler with statement trace, OLEDB errors, errors, audit logon, logoff, logon failed. See if you see any captured when you run the link query.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image tanglesoft commented ·
Helps if I read the question. Can you script out your linked server connection.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.