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

• Microsoft SQL Server, versions 6.0 & 6.5

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 37000. For example:

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

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

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

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.