INF: Tracing SQL Generated by MS SQL Server ODBC Driver

ID: Q139655

The information in this article applies to:

SUMMARY

This article discusses how to trace the SQL generated by the Microsoft ODBC SQL Server Driver.


MORE INFORMATION

Being able to trace the SQL commands generated by a two-tier ODBC driver can sometimes help you determine if a problem lies in the ODBC SQL commands generated by the ODBC application or in the native SQL generated by the driver to implement the application's ODBC SQL commands.

The ODBC SDK itself provides two tools for monitoring the ODBC commands coming in from applications. The first tool is the trace facility of the ODBC Driver Manager, which is started from the ODBC Administrator. You can click the Options button in the main ODBC Administrator window, then select the trace options to start tracing all calls made to any ODBC data source on the client. This Driver Manager trace traces ODBC calls right after they come into the Driver Manager, and is helpful in debugging problems that the Driver Manager may have when connecting to a driver. This is a fairly minimal trace, however, and the second tool, ODBCSpy, is the one most commonly used to troubleshoot ODBC calls.

The ODBCSpy utility ships with the ODBC SDK and can be used to get a very informative trace of all the ODBC calls made to a specific ODBC data source. ODBCSpy traces calls as they are passed from the Driver Manger to the ODBC driver. It shows all of the parameters passed for each call to the driver, and the information returned from the driver. If an error is encountered, ODBCSpy calls SQLError for all error messages returned, and logs the full information about the errors in the trace.

There are also third-party ODBC packages that provide ODBC tracing tools.

ODBCSpy can tell you what commands are going into an ODBC driver, but they must rely on the native tracing functions of the backend data source to determine what SQL commands are generated by two-tier ODBC drivers, such as the Microsoft SQL Server ODBC Driver. All versions of Microsoft SQL Server provide trace flags to trace all the SQL commands coming into the server. The trace flags are documented in either Appendix A of the SQL Server 4.2 "Troubleshooting Guide" or Chapter 24 of the SQL Server 6.0 "Administrator's Companion." In addition to the server traces, SQL Server 6.5 also introduces a SQL Trace utility that can be used to trace the SQL commands as they arrive at the server. The SQL Trace utility is documented in the "What's New in SQL Server 6.5," Part 3, What's New for Administrators, SQL Trace. The following paragraphs will first discuss the trace flags, and then SQL Trace.

The 4032 trace flag traces the SQL commands coming in from the client. This trace can be returned to the client by also using the 3604 trace, or the SQL can be logged in SQL Server's error log by using the 3605 trace. The -1 trace flag will make the trace cover all clients connecting to the server, otherwise the trace is specific to the connection issuing the trace command. The command to turn on the traces is: 


   DBCC TRACEON(flag1,flag2,...,flagn)

For example:
Log all SQL commands from all clients to the errorlog: 

   DBCC TRACEON(4032,3605,-1)

Echo all SQL commands from this connection back to the client:

DBCC TRACEON(4032,3604)

The traces remain on until turned off with DBCC TRACEOFF, or until the SQL Server is stopped and restarted. The trace flags can also be turned on if SQL Server is started from the command line (see the SQL Server manuals for more information).

The SQL Server 4.2 errorlog is in C:\SQL\LOG if the SQL Server was installed using the setup defaults. For SQL Server 6.0, it is in C:\SQL60\LOG, and for SQL Server 6.5, it is in C:\MSSQL\LOG.

For ODBC clients it is usually best to log the SQL commands to the errorlog using the 3605 trace. If the troubleshooting is being done through ODBCTest, then it is fairly easy to issue: 

   SQLExecDirect(hstmt,"dbcc traceon(3605,4032)",SQL_NTS);

This can also be added to a program which calls the ODBC API directly and which is being interactively debugged. If the ODBC application opens multiple connections, or does not offer the ability to call the ODBC API directly, it is best to isolate the application so that it is the only application running against a SQL Server, then issue: 

  dbcc traceon (4032,3605, -1)

from either ODBCTest, the SQL Server ISQL/w utility, or any other utility that will allow the entry of ad hoc commands.

If the 4032 trace is sent to the SQL Server errorlog, it is best to read the errorlog with the Write application rather than Notepad because Write formats the output more clearly.

The SQL commands generated by the Microsoft SQL Server ODBC Driver are sent to the server in one of two ways, either as standard SQL commands or as SQL Server Remote Procedure Calls (RPCs). (Review the section on Remote Procedure Calls in the SQL Server 6.0 ODBC Driver help file DRVSSRVR.HLP if you want more information). The SQL Server 4.21a driver does not make as much use of RPCs as the SQL Server 6.0 driver. These will show up in two different formats in a 4032 trace. For example, the command: 

   SQLExecDirect(hstmt,
                 "exec parmproc @cntr=1, @string='abcde'",
                 SQL_NTS);

will be executed as a standard SQL command and will generate a 4032 trace of: 

   95/10/28 13:51:02.85 11 LangExec: 'parmproc @cntr=1, @string='abcde'

Executing the same procedure using the ODBC call syntax with the SQL Server 6.0 driver: 

   SQLExecDirect(hstmt,
                 "{ call parmproc (1,'abcde') }",
                 SQL_NTS);

will be executed as a SQL Server RPC and will generate a 4032 trace of: 

   execrpc: parmproc
     parm 0: numeric, len 2(17), value: 1
     parm 1: varchar, len 5(255), value: abcde

The SQL Server 6.5 "What's New in SQL Server 6.5" documentation section on SQL Trace discusses how to turn on filters to capture SQL commands coming into a SQL Server 6.5. Once a filter has been applied to an ODBC client, the SQL commands being sent to the server by the driver will be visible. The two sample executions of parmproc given above show up in the following format in SQL Trace: 

   -- 4/16/96 20:24:17.783 SQL (ID=7, SPID=13, User=sa(REDMOND\alanbr),
      App='Microsoft ODBC SDK v2.0', Host='ALANBR3'(bf) )

   exec parmproc @cntr = 1, @string = 'abcde'
   go

   -- 4/16/96 20:24:41.116 RPC (ID=7, SPID=13, User=sa(REDMOND\alanbr),

      App='Microsoft ODBC SDK v2.0', Host='ALANBR3'(bf) )

   parmproc 1, "abcde", 1, "abcde"
   go

Additional query words: 2.50 sql65 sql6 4.21 odbc utility 


Keywords          : kbinterop kbprg SSrvProg 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 14, 1999