INF: Processing DBCC Commands in ODBC applications
ID: Q145643
|
The information in this article applies to:
-
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);
The second SQLExecDirect will return SQL_SUCCESS_WITH_INFO, and a call to
SQLError() will then return one line of trace output:
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 of the other DBCC commands fall into the second class regarding the way
they return output. All of the other DBCC commands return
SQL_SUCCESS_WITH_INFO when the DBCC command is executed, and the
application can retrieve the output by calling SQLError() until it returns
SQL_NO_DATA_FOUND. For example, if an ODBC application does:
SQLExecDirect(hstmt, "dbcc checktable(authors)", SQL_NTS);
This SQLExecDirect will return SQL_SUCCESS_WITH_INFO and calls to
SQLError() will return the following lines:
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."
Additional query words:
sql65 odbc debug
Keywords : kbinterop kbprg SSrvProg SSrvTran_SQL
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbhowto