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 (892028)

MORE INFORMATION

Steps to reproduce the behavior

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

Use a IF block in a Transact-SQL batch

1. Open SQL Profiler.
2. Start a trace that is configured to capture the SQL:StmtStarting event and the SQL:StmtCompleted event from the Transact-SQL event class group.
3. 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.

EventClass	TextData
SQL:StmtStarting	use master
SQL:StmtCompleted	use master
SQL:StmtStarting	if (1 <> 1)
SQL:StmtCompleted	if (1 <> 1)
SQL:StmtStarting
SQL:StmtCompleted

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

Use a WHILE block in a Transact-SQL batch

1. Open SQL Profiler.
2. Start a trace that is configured to capture the SQL:StmtStarting event and the SQL:StmtCompleted event from the Transact-SQL event class group.
3. Open SQL Query Analyzer, and then run the following batch of SQL statements:

   use master
   while (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.

EventClass	TextData
SQL:StmtStarting	use master
SQL:StmtCompleted	use master
SQL:StmtStarting	while (1 <> 1)
SQL:StmtCompleted	while (1 <> 1)
SQL:StmtStarting
SQL:StmtCompleted

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

Use an IF block in a stored procedure

1. Open SQL Query Analyzer.
2. Run the following batch of SQL statements:

   use master
   go
   create procedure myTestProcedure as if (1 <> 1) print 'One is not one'

3. Open SQL Profiler.
4. Start a trace that is configured to capture the SP:StmtStarting event and the SP:StmtCompleted event from the Stored procedures event class group.
5. In SQL Query Analyzer, run the following statement:

   execute myTestProcedure

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

EventClass	TextData
SQL:StmtStarting	-- myTestProcedure if (1 <> 1)
SQL:StmtCompleted	-- myTestProcedure if (1 <> 1)
SQL:StmtStarting
SQL:StmtCompleted

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

Use a WHILE block in a stored procedure

1. Open SQL Query Analyzer.
2. Run the following batch of SQL statements:

   use master
   go
   create procedure myTestProcedure as while (1 <> 1) print 'One is not one'
   

3. Open SQL Profiler.
4. Start a trace that is configured to capture the SP:StmtStarting event and the SP:StmtCompleted event from the Stored procedures event class group.
5. In SQL Query Analyzer, run the following statement:

   execute myTestProcedure

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

EventClass	TextData
SQL:StmtStarting	-- myTestProcedure while (1 <> 1)
SQL:StmtCompleted	-- myTestProcedure while (1 <> 1)
SQL:StmtStarting
SQL:StmtCompleted

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