Making Sense of SQLOUT.TXT

Mike Wolfe -

If you want to trace ODBC commands from Access, there are three steps:

  1. Enable ODBC Trace SQL Mode in the registry
  2. Find and open the sqlout.txt file
  3. Make sense of what you see in sqlout.txt (read on!)

Overview of SQLOUT.TXT

The contents of sqlout.txt are a less detailed version of what you would see using XEvent Profiler in SSMS.  Here's a screenshot of one of my sqlout.txt files:

Notice a few things about this screenshot:

  • There is a blank line separating each entry
  • Each line begins with an ODBC function: SQLPrepare, SQLExecute, SQLExecDirect, etc.
  • SQLExecute lines include an explanatory note:
      • (GOTO BOOKMARK)
      • (MULTI-ROW FETCH)
      • etc.
  • SQLPrepare and SQLExecDirect lines include the T-SQL passed to SQL Server
  • T-SQL statements contain question marks (?) as parameter placeholders
  • The value of the parameters is not logged in sqlout.txt

SQLPrepare

From the documentation:

SQLPrepare prepares an SQL string for execution.

If Access will be executing the same query more than once, it starts by calling the SQLPrepare function so SQL Server can build and save a query plan for future use.

SQLExecute

From the documentation:

SQLExecute executes a prepared statement, using the current values of the parameter marker variables if any parameter markers exist in the statement.

The SQLPrepare statement does not return data from the database; it only creates the query plan.  Running SQLExecute retrieves data using a previously compiled query plan.

In practice, the SQLPrepare and SQLExecute ODBC functions are often executed on SQL Server using the sp_prepexec stored procedure, which combines the two functions into a single statement.

SQLExecute: (GOTO BOOKMARK)

This line represents Access requesting a single record from a prepared query.

SQLExecute: (MULTI-ROW FETCH)

This line represents Access requesting multiple records (batches of ten, usually) from a prepared query.

Every ODBC request carries a certain amount of overhead.  Pulling multiple records at a time reduces the total overhead versus pulling them one at a time.  Generally speaking, large blocks of (MULTI-ROW FETCH) lines will perform better than large blocks of (GOTO BOOKMARK) lines.

Remember:

  • (GOTO BOOKMARK): one record retrieved
  • (MULTI-ROW FETCH): ten records retrieved

If you are seeing large blocks of (GOTO BOOKMARK) lines, you may be suffering from the empty RecordSource bug that Access MVP Philipp Stiefel identified.

SQLExecDirect

From the documentation:

SQLExecDirect executes a preparable statement, using the current values of the parameter marker variables if any parameters exist in the statement. SQLExecDirect is the fastest way to submit an SQL statement for one-time execution.

If Access only needs to execute a query once, it will use the SQLExecDirect function.  This is used for things like inserting or deleting a single record, as well as retrieving an entire table's worth of unique index values to aid in form navigation (with multi-row fetch used to retrieve all requested field values for just the visible rows in a continuous form).

SQLExecDirect: SELECT IDENT_CURRENT()

This statement gets called immediately after a record gets inserted on a table with an autonumber column.  

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Note in the documentation quoted above, the value returned is for any session and any scope.  Thus, in an environment with a lot of inserts (or just really bad luck), it's possible for this function to return the wrong value.

Further reading

Long-time Access MVP Ben Clothier recently wrote an excellent six-part series on ODBC tracing:

  1. Why should we trace ODBC? How will it help me?
  2. What’s Access doing when we browse and look at records in a ODBC linked table?
  3. How does Access choose a key for sorting and selecting?
  4. What’s Access doing when a user makes changes to data on an ODBC linked table?
  5. Filtering the recordset
  6. Effect of joins in a recordset

External references

ODBC Function Summary - ODBC API Reference
ODBC Function Summary
Microsoft DocsDavid-Engel
Setting the Recordsource at runtime disables ODBC Multi-Row-Fetch for continuous form
This super esoteric issue might be a factor when you experience lagging screen updates in your continuous form bound to an ODBC table from an SQL Server on Azure.
CodekabinettPhilipp Stiefel

Referenced articles

Using ProcMon to Troubleshoot Registry Calls
Finding the correct registry keys for JetShowPlan and ODBC TraceSqlMode can be tricky. Let ProcMon take the guesswork out of the process.
No Longer SetMike Wolfe
3 Ways to Find sqlout.txt
Finding ODBC TraceSQLMode’s sqlout.txt file can be deceptively difficult. Here are three approaches to make it easy. At least one is guaranteed to work.
No Longer SetMike Wolfe
How Access Reads Data From SQL Server
How does Microsoft Access pull data from SQL Server? You’ll be amazed when we pull back the curtain to expose how Access interacts with SQL Server.
No Longer SetMike Wolfe

Image by Augusto Ordóñez from Pixabay