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 Microsoft® 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_NTS);
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][ODBC SQL Server Driver][SQL Server]
Table Scan"
SQL Server version 7.0 replaces 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][ODBC SQL Server Driver][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][ODBC SQL Server Driver][SQL Server]
Table: testshow scan count 1, logical reads: 1,
physical reads: 0."
DBCC statements return data to an ODBC application in two ways: Trace flag output and DBCC execution output.
An application can turn on various trace flags using the DBCC statement. No data is returned by the DBCC statement that turns on the trace flag, but the trace data is returned on subsequent SQL statements.
For example, if the application sets on a 3604 trace flag along with another flag or flags that return output, subsequent calls to SQLExecDirect or SQLExecute return SQL_SUCCESS_WITH_INFO, and the application can retrieve the trace flag output by calling SQLGetDiagRec until it returns SQL_NO_DATA:
SQLExecDirect(hstmt, "DBCC TRACEON(3604, 4032)", SQL_NTS);
After the above SQLExecDirect call completes, the following subsequent call to SQLExecDirect returns SQL_SUCCESS_WITH_INFO:
SQLExecDirect(hstmt, "SELECT * FROM authors", SQL_NTS);
Calling SQLGetDiagRec returns:
szSqlState = "01000", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
96/02/02 11:08:45.26 10 LangExec: 'SELECT * FROM authors"
All other DBCC statements return data when they are executed. 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][ODBC SQL Server Driver][SQL Server]
Checking authors"
szSqlState = "01000", *pfNativeError = 2579,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
The total number of data pages in this table is 1."
szSqlState = "01000", *pfNativeError = 7929,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Table has 23 data rows."
szSqlState = "01000", *pfNativeError = 2528
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
DBCC execution completed. If DBCC printed error messages,
see your System Administrator."
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] [ODBC SQL Server Driver][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] [ODBC SQL Server Driver][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] [ODBC SQL Server Driver][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 SQLMoreResults positions 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.