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
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...
Has your SQL Server service user got the right rights to access the Access database?
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
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.
Helps if I read the question. Can you script out your linked server connection.
15 People are following this question.
