OLE Automation Return Codes and Error Information

The OLE Automation system stored procedures return an int return code that is the HRESULT returned by the underlying OLE Automation operation. An HRESULT of 0 indicates success. A nonzero HRESULT is an OLE error code of the hexadecimal form 0x800nnnnn, but when returned as an int value in a stored procedure return code, HRESULT has the form 214nnnnnnn.

For example, passing an invalid object name (SQLDMO.Xyzzy) to sp_OACreate causes the procedure to return an int HRESULT of 2147221005, which is 0x800401f3 in hexadecimal.

You can use CONVERT(binary(4), @hresult) to convert an int HRESULT to a binary value. However, using CONVERT(char(10), CONVERT(binary(4), @hresult)) results in an unreadable string, because each byte of the HRESULT is converted to a single ASCII character. You can use the following sample HexToChar stored procedure to convert an int HRESULT to a char value that contains a readable hexadecimal string.

USE AdventureWorks;
GO
IF EXISTS(SELECT name FROM sys.objects
          WHERE name = N'sp_HexToChar')
    DROP PROCEDURE HexToChar;
GO
CREATE PROCEDURE dbo.sp_HexToChar
    @BinValue varbinary(255),
    @HexCharValue nvarchar(255) OUTPUT
AS
    DECLARE @CharValue nvarchar(255);
    DECLARE @Position int;
    DECLARE @Length int;
    DECLARE @HexString nchar(16);
    SELECT @CharValue = N'0x';
    SELECT @Position = 1;
    SELECT @Length = DATALENGTH(@BinValue);
    SELECT @HexString = N'0123456789ABCDEF';
    WHILE (@Position <= @Length)
    BEGIN
        DECLARE @TempInt int;
        DECLARE @FirstInt int;
        DECLARE @SecondInt int;
        SELECT @TempInt = CONVERT(int, SUBSTRING(@BinValue,@Position,1));
        SELECT @FirstInt = FLOOR(@TempInt/16);
        SELECT @SecondInt = @TempInt - (@FirstInt*16);
        SELECT @CharValue = @CharValue +
            SUBSTRING(@HexString, @FirstInt+1, 1) +
            SUBSTRING(@HexString, @SecondInt+1, 1);
        SELECT @Position = @Position + 1;
    END
    SELECT @HexCharValue = @CharValue;
GO
DECLARE @BinVariable varbinary(35);
DECLARE @CharValue nvarchar(35);

SET @BinVariable = 123456;

EXECUTE dbo.sp_HexToChar
    @binvalue = @BinVariable,
    @HexCharValue = @CharValue OUTPUT;

SELECT @BinVariable AS BinaryValue,
    @CharValue AS CharacterRep;
GO

You can use the following sample stored procedure, sp_displayoaerrorinfo to display OLE Automation error information when one of the OLE Automation procedures returns a nonzero HRESULT return code. This sample stored procedure uses HexToChar.

CREATE PROCEDURE dbo.sp_DisplayOAErrorInfo
    @Object int,
    @HResult int
AS
    DECLARE @Output nvarchar(255);
    DECLARE @HRHex nchar(10);
    DECLARE @HR int;
    DECLARE @Source nvarchar(255);
    DECLARE @Description nvarchar(255);
    PRINT N'OLE Automation Error Information';
    EXEC HexToChar @HResult, @HRHex OUT;
    SELECT @Output = N'  HRESULT: ' + @HRHex;
    PRINT @Output;
    EXEC @HR = sp_OAGetErrorInfo
        @Object,
        @Source OUT,
        @Description OUT;
    IF @HR = 0
    BEGIN
        SELECT @Output = N'  Source: ' + @Source;
        PRINT @Output;
        SELECT @Output = N'  Description: '
               + @Description;
        PRINT @Output;
    END
    ELSE
    BEGIN
       PRINT N' sp_OAGetErrorInfo failed.';
       RETURN;
    END
GO

See Also

Other Resources

sp_OAGetErrorInfo (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance