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