Share via

Processing Statements That Generate Messages

  • Article

The Transact-SQL SET statement options STATISTICS TIME and STATISTICS IO are used to get information that aids in diagnosing long-running queries. Earlier versions of SQL Server also support the SHOWPLAN option for analyzing query plans. An ODBC application can set these options by executing the following statements:

SQLExecDirect(hstmt, "SET SHOWPLAN ON", SQL_NTS);  
SQLExecDirect(hstmt, "SET STATISTICS TIME ON", SQL_NTS90  
);  
SQLExecDirect(hstmt, "SET STATISTICS IO ON", SQL_NTS);

When SET STATISTICS TIME or SET SHOWPLAN are ON, SQLExecute and SQLExecDirect return SQL_SUCCESS_WITH_INFO, and, at that point, the application can retrieve the SHOWPLAN or STATISTICS TIME output by calling SQLGetDiagRec until it returns SQL_NO_DATA. Each line of SHOWPLAN data comes back in the format:

szSqlState="01000", *pfNativeError=6223,  
szErrorMsg="[Microsoft][SQL Server Native Client][SQL Server]   
              Table Scan"

SQL Server version 7.0 replaced the SHOWPLAN option with SHOWPLAN_ALL and SHOWPLAN_TEXT, both of which return output as a result set, not a set of messages.

Each line of STATISTICS TIME comes back in the format:

szSqlState="01000", *pfNativeError= 3613,  
szErrorMsg="[Microsoft][SQL Server Native Client][SQL Server]  
   SQL Server Parse and Compile Time: cpu time = 0 ms."

The output of SET STATISTICS IO is not available until the end of a result set. To get STATISTICS IO output, the application calls SQLGetDiagRec at the time SQLFetch or SQLFetchScroll returns SQL_NO_DATA. The output of STATISTICS IO comes back in the format:

szSqlState="01000", *pfNativeError= 3615,  
szErrorMsg="[Microsoft][ SQL Server Native Client][SQL Server]  
   Table: testshow  scan count 1,  logical reads: 1,  
   physical reads: 0."

Using DBCC Statements

DBCC statements return their data as messages, not result sets. SQLExecDirect or SQLExecute return SQL_SUCCESS_WITH_INFO, and the application retrieves the output by calling SQLGetDiagRec until it returns SQL_NO_DATA.

For example, the following statement returns SQL_SUCCESS_WITH_INFO:

SQLExecDirect(hstmt, "DBCC CHECKTABLE(Authors)", SQL_NTS);

Calls to SQLGetDiagRec return:

szSqlState = "01000", *pfNativeError = 2536,  
szErrorMsg="[Microsoft][ SQL Server Native Client][SQL Server]  
   Checking authors"  
szSqlState = "01000", *pfNativeError = 2579,  
szErrorMsg="[Microsoft][ SQL Server Native Client][SQL Server]  
   The total number of data pages in this table is 1."  
szSqlState = "01000", *pfNativeError = 7929,  
szErrorMsg="[Microsoft][ SQL Server Native Client][SQL Server]  
   Table has 23 data rows."  
szSqlState = "01000", *pfNativeError = 2528  
szErrorMsg="[Microsoft][ SQL Server Native Client][SQL Server]  
   DBCC execution completed. If DBCC printed error messages,  
   see your System Administrator."

Using PRINT and RAISERROR Statements

Transact-SQL PRINT and RAISERROR statements also return data by calling SQLGetDiagRec. PRINT statements cause the SQL statement execution to return SQL_SUCCESS_WITH_INFO, and a subsequent call to SQLGetDiagRec returns a SQLState of 01000. A RAISERROR with a severity of ten or lower behaves the same as PRINT. A RAISERROR with a severity of 11 or higher causes the execute to return SQL_ERROR, and a subsequent call to SQLGetDiagRec returns SQLState 42000. For example, the following statement returns SQL_SUCCESS_WITH_INFO:

SQLExecDirect (hstmt, "PRINT  'Some message' ", SQL_NTS);

Calling SQLGetDiagRec returns:

szSQLState = "01000", *pfNative Error = 0,  
szErrorMsg= "[Microsoft] [SQL Server Native Client][SQL Server]  
   Some message"

The following statement returns SQL_SUCCESS_WITH_INFO:

SQLExecDirect (hstmt, "RAISERROR ('Sample error 1.', 10, -1)",  
   SQL_NTS)

Calling SQLGetDiagRec returns:

szSQLState = "01000", *pfNative Error = 50000,  
szErrorMsg= "[Microsoft] [SQL Server Native Client][SQL Server]  
   Sample error 1."

The following statement returns SQL_ERROR:

SQLExecDirect (hstmt, "RAISERROR ('Sample error 2.', 11, -1)", SQL_NTS)

Calling SQLGetDiagRec returns:

szSQLState = "42000", *pfNative Error = 50000,  
szErrorMsg= "[Microsoft] [SQL Server Native Client][SQL Server]  
   Sample error 2."

The timing of calling SQLGetDiagRec is critical when output from PRINT or RAISERROR statements is included in a result set. The call to SQLGetDiagRec to retrieve the PRINT or RAISERROR output must be made immediately after the statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is straightforward when only a single SQL statement is executed, as in the examples above. In these cases, the call to SQLExecDirect or SQLExecute returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLGetDiagRec can then be called. It is less straightforward when coding loops to handle the output of a batch of SQL statements or when executing SQL Server stored procedures.

In this case, SQL Server returns a result set for every SELECT statement executed in a batch or stored procedure. If the batch or procedure contains PRINT or RAISERROR statements, the output for these is interleaved with the SELECT statement result sets. If the first statement in the batch or procedure is a PRINT or RAISERROR, the SQLExecute or SQLExecDirect returns SQL_SUCCESS_WITH_INFO or SQL_ERROR, and the application needs to call SQLGetDiagRec until it returns SQL_NO_DATA to retrieve the PRINT or RAISERROR information.

If the PRINT or RAISERROR statement comes after an SQL statement (such as a SELECT statement), then the PRINT or RAISERROR information is returned when SQLMoreResultspositions on the result set containing the error. SQLMoreResults returns SQL_SUCCESS_WITH_INFO or SQL_ERROR depending on the severity of the message. Messages are retrieved by calling SQLGetDiagRec until it returns SQL_NO_DATA.

See Also

Handling Errors and Messages