ODBC Tracing
Another tool frequently used for troubleshooting ODBC problems is ODBC Tracing. ODBC Tracing is a service provided by the driver manager and records every ODBC call made into a text file. ODBC Tracing can be turned on through the ODBC Data Source Administrator, in the “Tracing” tab.
To trace an application, make sure the application is stopped (the process is not loaded), turn on the tracing through Data Source Administrator, and then start the application. When you are done tracing, be sure to turn tracing off. As you will notice when tracing an application, the act of tracing substantially hinders performance, and many SP’s and customers alike have been baffled why their application was so slow when in fact tracing had been left on.
The trace will be stored in a log file specified in Data Source. If you run tracing several times consecutively without changing the trace file, each new trace will be appended to the end of the old trace. Trace files can grow very large quite quickly, so to make troubleshooting easier it is best to minimize the number of application ODBC calls made to reproduce the problem.
Demonstration:
1. Open ODBC Data Source Administrator. Click on the “Tracing” tab.
2. Click “Start Tracing Now”
3. Open ODBCTest.
4. Click on the handshake icon in the toolbar (3rd to the left).
5. Choose your DSN (TestDSN1) and click “OK”
6. In the upper portion of the new window that appears, type the following (all text INSIDE of quotes only!): “USE PUBS; SELECT * FROM AUTHORS”
7. Go to the “Stmt” menu and select “SQLExecDirect”. Click “OK”. You will see in the window that the function executed and returned the value “1” (SQL_SUCCESS_WITH_INFO).
8. Go to the “Results” menu and select “Get Data All”. The results window will now show all data from the pubs table. Notice how this operation is quite slow. This is due to tracing being turned on.
9. Now go back to ODBC Administrator and turn tracing off (“Stop Tracing Now” button).
10. Open the trace file (C:\sql.log) with Notepad. Examine this log. These are the calls executed against the SQL Server driver by our actions in ODBCTest. Notice that ODBC Tracing records the application name (far left), Thread ID, ODBC function call, return value, and all parameters.
To see if there are any errors in an ODBC Trace file, open the file with Notepad, and do a “Find” on the string “DIAG”. This string precedes any diagnostic information reported in the log. Note, however, that the existence of diagnostic strings in the text does not necessarily indicate errors, as some diagnostic strings are informational only.