Using sp_DisplayOAErrorInfo

SQL Server's sp_OAGetErrorInfo stored procedure provides all the information you need to pass up to your application's middle and client tiers when an error occurs. However, writing an error handler to pass this error information can be time-consuming. In addition, if you don't interactively run the procedure that automates the COM object but instead schedule the procedure with the SQL Agent (SQL Executive in SQL Server 6.5), you'll want to log the information somehow.

Fortunately, SQL Server Books Online (BOL) includes a sample stored procedure called sp_DisplayOAErrorInfo. This procedure calls sp_OAGetErrorInfo for you and combines the returned values into formatted strings. The procedure then passes these strings to a T-SQL print statement, which outputs them either as messages to an ADO or RDO client when you run the procedure interactively or as entries in the SQL Agent log when you schedule the procedure.

To use sp_DisplayOAErrorInfo, place the following code after a call to any of the automation procedures. (This example assumes that @Object holds the object's object token and that @hr holds the return value from the automation call.)

If @hr <> 0
BEGIN
Exec sp_DisplayOAErrorInfo @Object, @hr
Return
END

To find stored procedure sp_DisplayOAErrorInfo's sample source code, look in BOL under the heading "sp_OAGetErrorInfo" for SQL Server 7.0 or "HRESULT Return Codes" for SQL Server 6.5.