Microsoft KB Archive/892028

= The TextData column may be empty for the SP:StmtStarting, SP:StmtCompleted, SQL:StmtStarting, and SQL:StmtCompleted events when you use SQL Profiler to capture SQL Server traces in SQL Server 2000 =

Article ID: 892028

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Personal Edition

-





SYMPTOMS
When you use SQL Profiler to capture SQL Server traces in Microsoft SQL Server 2000, the TextData column may be empty for the following events:
 * SP:StmtStarting
 * SP:StmtCompleted
 * SQL:StmtStarting
 * SQL:StmtCompleted



CAUSE
This problem may occur if the following conditions are true:
 * An IF, ELSE, or WHILE control block is used at the end of a Transact-SQL batch or a stored procedure.
 * No additional statements exist after the control block.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
To reproduce this behavior, use one of the following methods.

Use a IF block in a Transact-SQL batch
 Open SQL Profiler. Start a trace that is configured to capture the SQL:StmtStarting event and the SQL:StmtCompleted event from the Transact-SQL event class group.  Open SQL Query Analyzer, and then run the following batch of SQL statements: use master if (1 <> 1) print 'One is not one' 

After the batch runs, you notice three pairs of SQL:StmtStarting and SQL:StmtCompleted events. The following information appears in SQL Profiler.

Notice that the TextData column is empty for the third event pair.

Use a WHILE block in a Transact-SQL batch
 Open SQL Profiler. Start a trace that is configured to capture the SQL:StmtStarting event and the SQL:StmtCompleted event from the Transact-SQL event class group.</li>  Open SQL Query Analyzer, and then run the following batch of SQL statements: use master while (1 <> 1) print 'One is not one' </li></ol>

After the batch runs, you notice three pairs of SQL:StmtStarting and SQL:StmtCompleted events. The following information appears in SQL Profiler.

Notice that the TextData column is empty for the third event pair.

Use an IF block in a stored procedure
 Open SQL Query Analyzer.</li>  Run the following batch of SQL statements: use master go create procedure myTestProcedure as if (1 <> 1) print 'One is not one' </li> Open SQL Profiler.</li> Start a trace that is configured to capture the SP:StmtStarting event and the SP:StmtCompleted event from the Stored procedures event class group.</li>  In SQL Query Analyzer, run the following statement: execute myTestProcedure </li></ol>

After the stored procedure runs, you notice two pairs of SQL:StmtStarting and SQL:StmtCompleted events. The following information appears in SQL Profiler.

Notice that the TextData column is empty for the second event pair.

Use a WHILE block in a stored procedure
 Open SQL Query Analyzer.</li>  Run the following batch of SQL statements: use master go create procedure myTestProcedure as while (1 <> 1) print 'One is not one' </li> Open SQL Profiler.</li> Start a trace that is configured to capture the SP:StmtStarting event and the SP:StmtCompleted event from the Stored procedures event class group.</li>  In SQL Query Analyzer, run the following statement: execute myTestProcedure </li></ol>

After the stored procedure runs, you notice two pairs of SQL:StmtStarting and SQL:StmtCompleted events. The following information appears in SQL Profiler.

Notice that the TextData column is empty for the second event pair.

Additional query words: T-SQL

Keywords: kbprb kbtshoot KB892028

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.