0

There's a recurring issue at my company, where queries will be left hanging overnight and tie up the nightly datamart load.

A common claim, when the person is tracked down as to why a SQL query was left running overnight, is that an Access SQL passthrough query had stopped running before they left, and that Access was just left on with nothing running. They insist nothing was running.

Are they mistaken/fibbing/unobservant, or do they need to close Access out to kill the connection? Is this a quirk in Access, or is a query hanging (despite it not looking like it's executing) an impossible scenario?

Onohalp
  • 43
  • 5

1 Answers1

0

Two possible reasons:

1) If a large recordset is returned by the query, Access will keep loading additional records for some time, not all at one, leaving the table in a ASYNC_NETWORK_IO wait state, i.e. locked.
See https://stackoverflow.com/a/35176081/3820271

2) The ODBC refresh interval - by default every 1500 seconds (25 minutes) Access will requery loaded data.

Note that neither of this is specific to PassThrough queries, it also applies to Access queries on linked ODBC tables.

So yes, you should tell users to close Access over night.

Andre
  • 26,751
  • 7
  • 36
  • 80