INF: Processing DBCC Commands in ODBC applications

• Microsoft SQL Server, versions 6.0 & 6.5
• Microsoft Open Database Connectivity

SUMMARY

This article discusses how the ODBC applications that use the Microsoft SQL Server ODBC Driver can process the output of DBCC commands. SQL Server drivers from other vendors may return DBCC information differently, and you should refer to the documentation for those drivers.

MORE INFORMATION

Microsoft SQL Server applications can use the Transact-SQL DBCC commands to get additional information from the server, such as information on the choices made by the server's optimizer, locking information, table consistency, and so forth.

All the information returned to applications from DBCC commands does not come back in a result set. In DB-library applications, the output comes back to the application's message handler. The Microsoft SQL Server ODBC driver returns the output by returning SQL_SUCCESS_WITH_INFO on the ODBC function that executes the command. The application can then retrieve the DBCC output by calling the SQLError() function. There are two main classes of DBCC commands with regard to the time at which the application should expect SQL_SUCCESS_WITH_INFO and call SQLError().

The first class is the DBCC output generated through the DBCC TRACEON trace flags. Some of the DBCC trace flags generate trace information whenever the server processes an SQL command. For example, the 4032 flag traces each SQL command as it is received at the server, and the 310 flag shows information about the join order used. The output destination of the trace flags is controlled with the 3605/3604 trace flags. If the 3604 flag is turned on, then trace flag output is returned to the application; if the 3605 flag is on, then trace output is sent to the SQL Server errorlog.

When an ODBC application sets the 3604 flag in conjunction with a trace flag that generates (like 4032 or 310), then SQLExecute or SQLExecDirect for each SQL command will return SQL_SUCCESS_WITH_INFO. When the application then calls SQLError(), each call to SQLError() will return a line of the trace output. Many of the flags will return multiple lines of output, so the application should call SQLError() until it returns SQL_NO_DATA_FOUND.

For example, if an ODBC application does:

  SQLExecDirect(hstmt, "dbcc traceon(3604, 4032)", SQL_NTS);
  SQLExecDirect(hstmt, "select * from authors", SQL_NTS);

   szSqlState = "01000", *pfNativeError = 0,
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
         96/02/02 11:08:45.26 10 LangExec: 'select * from authors"

   SQLExecDirect(hstmt, "dbcc checktable(authors)", SQL_NTS);

   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."