0

Microsoft® Access® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20888) 64-bit Microsoft SQL Server 2019 - 15.0.4261.1 (X64) Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 System Type 64 bit operating system, x64-based processor

I've created an ODBC 64-bit file DSN connection for a MS Access Pass-Through Query to a SQL Server database. I've got a large query that runs on the client side in around five minutes; the query appears to run and correctly return the requested records. The ODBC Timeout is set to 540 (seconds). The problem is that the server shows that the query ran for over forty-five minutes before I was contacted by a DBA. I terminated Access and that severed the connection.

Would anyone know why this might happen or how I could troubleshoot?

C.George
  • 55
  • 5
  • Is the pass-through query a single statement? If not, make sure the batch has `SET NOCOUNT ON`. – Dan Guzman Jan 27 '23 at 22:41
  • If the query returns many records, Access may load only the first part, until you scroll to the end of the result set. This is a known issue for linked ODBC tables and can cause ASYNC_NETWORK_IO locking on the table. ([link](https://stackoverflow.com/questions/12026199/ms-access-holds-locks-on-table-rows-indefinitely)) -- Not sure if this applies here. – Andre Jan 28 '23 at 17:16
  • This is what happened here. Would you mind putting it in the form of an answer so I can select it as the best answer to my question? – C.George Jan 30 '23 at 19:59
  • For future reference, "tag" a name like this: @C.George to notify them of your comment. – Andre Feb 02 '23 at 01:48

1 Answers1

1

You can trace a query to see when different phases of the query complete. Typically, when something impossible seems to be going on, when you look closer there's some easy explanation. Is there a transaction that's left open? Does running the query trigger updating statistics? Why does the DBA think the query keeps running? There's nothing unique about ODBC querying the database that would allow it to keep running without terminating. A first step might be to just run the query directly within SQL Studio and see if you can reproduce.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-never-ending-query?tabs=2008-2014

user3112728
  • 395
  • 1
  • 12
  • Thanks! Was able to use this to determine when locks were in place and when they were released. It appears that, from the Access GUI, they were in place for pass-through query datasheet views until the last record was reached (ctrl+end); and for VBA pass-through query recordset calls until recordset.movelast was executed. All the best! – C.George Feb 01 '23 at 04:17