Microsoft KB Archive/140696

= INF: RAISERROR, PRINT, and the MS SQL Server ODBC Driver =

Article ID: 140696

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q140696



SUMMARY
This article discusses how the Microsoft SQL Server ODBC 2.x Driver returns the output of Transact-SQL RAISERROR or PRINT statements to an ODBC application.



MORE INFORMATION
In Transact-SQL, the PRINT statement can be used to return a user-defined message to a DB-Library application's message handler routine. The RAISERROR statement can be used to raise a user-defined error, which is reported to a DB-Library application's error handler.

ODBC does not have the concept of message or error handlers like the ones in DB-Library. The Microsoft ODBC SQL Server Driver instead returns the output of PRINT and RAISERROR statements through the SQLError function. PRINT statements will cause the SQL statement execution to return SQL_SUCCESS_WITH_INFO, and a subsequent call to SQLError returns a SQLState of 01000. A RAISERROR with a severity of ten or lower will behave the same as PRINT. A RAISERROR with a severity of 11 will cause the execute to return SQL_ERROR and a subsequent call to SQLError returns SQLState
 * 1) For example:
 * 2) SQLExecDirect (hstmt, "PRINT 'Some message' ", SQL_NTS); Returns SQL_SUCCESS_WITH_INFO, SQLError reports:

szSQLState = "01000", *pfNative Error = 0,

szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server] Some message"
 * 1) SQLExecDirect (hstmt, "RAISERROR ('Sample error 1.', 11, -1)", SQL_NTS)

Returns SQL_ERRORm SQLError reports:

szSQLState = "37000", *pfNative Error = 50000,

szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server] Sample error 1."
 * 1) SQLExecDirect (hstmt, "RAISERROR ('Sample error 2.', 10, -1)", SQL_NTS)

Returns SQL_SUCCESS_WITH_INFO, SQLError reports:

szSQLState = "01000", *pfNative Error = 50000,

szErrorMsg= "[Microsoft] [ODBC SQL Server Driver][SQL Server] Sample error 2."

The timing of calling SQLError is critical when output from PRINT or RAISERROR statements may be included in a result set. The call to SQLError to retrieve the PRINT or RAISERROR output must be made right after the statement that receives the SQL_SUCCESS_WITH_INFO or SQL_ERROR return. 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 will return SQL_ERROR or SQL_SUCCESS_WITH_INFO, and SQLError can then be called. It is less straightforward in coding loops to handle the output of a batch of SQL commands, or when executing SQL Server stored procedures.

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 will be 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 will return SQL_SUCCESS_WITH_INFO or SQL_ERROR and the application will need to call SQLError until it returns SQL_NO_DATA_FOUND to retrieve the PRINT or RAISERROR information. If the PRINT or RAISERROR statement comes after other SQL statements (such as a select), then the PRINT or RAISERROR information will be returned when SQLFetch or SQLExtendedFetch for the result set before the PRINT or RAISERROR returns SQL_NO_DATA_FOUND or SQL_ERROR.

For example, when processing the following procedure:

CREATE PROCEDURE odbcproc AS PRINT 'First PRINT Message.' SELECT name FROM sysusers WHERE suid < 2 PRINT 'Second PRINT Message.' GO

The SQLExecute or SQLExecDirect call will return SQL_SUCCESS_WITH_INFO and a call to SQLError at that point will return the first print message. If the ODBC application then processes through the result set using SQLFetch, the application can get the second print statement by calling SQLError when SQLFetch returns SQL_NO_DATA_FOUND.

For additional information on how ODBC drivers later than 2.x handle these statements, click the article number below to view the article in the Microsoft Knowledge Base:

280109 INF: PRINT, RAISERROR, DBCC Commands and ODBC Driver for SQL Server 2000 and SQL Server 7.0

Additional query words: sql6 winnt odbc debug

Keywords: kbinterop kbenv KB140696

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.