SQL Server

Making Sense of SQLOUT.TXT

You've enabled ODBC Trace SQL Mode and created a sqlout.txt file. But do you know what to do with it now? Let's explore.

Mike Wolfe

Mar 9, 2022 • 4 min read

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

Enable ODBC Trace SQL Mode in the registry
Find and open the sqlout.txt file
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.

Making Sense of SQLOUT.TXT

Mike Wolfe