You are currently offline, waiting for your internet to reconnect

INF: Tracing SQL Generated by MS SQL Server ODBC Driver

This article was previously published under Q139655
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article discusses how to trace the SQL generated by the MicrosoftODBC SQL Server Driver.
Being able to trace the SQL commands generated by a two-tier ODBC drivercan sometimes help you determine if a problem lies in the ODBC SQL commandsgenerated by the ODBC application or in the native SQL generated by thedriver to implement the application's ODBC SQL commands.

The ODBC SDK itself provides two tools for monitoring the ODBC commandscoming in from applications. The first tool is the trace facility of theODBC Driver Manager, which is started from the ODBC Administrator. You canclick the Options button in the main ODBC Administrator window, then selectthe trace options to start tracing all calls made to any ODBC data sourceon the client. This Driver Manager trace traces ODBC calls right after theycome into the Driver Manager, and is helpful in debugging problems that theDriver Manager may have when connecting to a driver. This is a fairlyminimal trace, however, and the second tool, ODBCSpy, is the one mostcommonly used to troubleshoot ODBC calls.

The ODBCSpy utility ships with the ODBC SDK and can be used to get a veryinformative trace of all the ODBC calls made to a specific ODBC datasource. ODBCSpy traces calls as they are passed from the Driver Manager tothe ODBC driver. It shows all of the parameters passed for each call to thedriver, and the information returned from the driver. If an error isencountered, ODBCSpy calls SQLError for all error messages returned, andlogs 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 theymust rely on the native tracing functions of the backend data source todetermine what SQL commands are generated by two-tier ODBC drivers, such asthe Microsoft SQL Server ODBC Driver. All versions of Microsoft SQL Serverprovide 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 Server6.5 also introduces a SQL Trace utility that can be used to trace the SQLcommands as they arrive at the server. The SQL Trace utility is documentedin the "What's New in SQL Server 6.5," Part 3, What's New forAdministrators, SQL Trace. The following paragraphs will first discuss thetrace 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, orthe 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 theserver, otherwise the trace is specific to the connection issuing the tracecommand. 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 untilthe SQL Server is stopped and restarted. The trace flags can also beturned on if SQL Server is started from the command line (see the SQLServer manuals for more information).

The SQL Server 4.2 errorlog is in C:\SQL\LOG if the SQL Server wasinstalled using the setup defaults. For SQL Server 6.0, it is inC:\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 theerrorlog using the 3605 trace. If the troubleshooting is being donethrough 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 directlyand which is being interactively debugged. If the ODBC applicationopens multiple connections, or does not offer the ability to call theODBC API directly, it is best to isolate the application so that it isthe 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 otherutility that will allow the entry of ad hoc commands.

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

The SQL commands generated by the Microsoft SQL Server ODBC Driver are sentto the server in one of two ways, either as standard SQL commands or as SQLServer Remote Procedure Calls (RPCs). (Review the section on RemoteProcedure Calls in the SQL Server 6.0 ODBC Driver help file DRVSSRVR.HLP ifyou want more information). The SQL Server 4.21a driver does not make asmuch use of RPCs as the SQL Server 6.0 driver. These will show up in twodifferent 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 traceof:
   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 Server6.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 onSQL Trace discusses how to turn on filters to capture SQL commands cominginto 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 followingformat 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				
2.50 sql65 sql6 4.21 odbc utility

Article ID: 139655 - Last Review: 10/28/2003 19:29:11 - Revision: 3.0

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbinterop kbprogramming KB139655