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

ID: Q140696

The information in this article applies to:

SUMMARY

This article discusses how the Microsoft SQL Server ODBC 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"


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


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

Additional query words: sql6 winnt odbc debug 


Keywords          : kbenv kbinterop SSrvProg 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 23, 1999