HRESULT Return Codes

These procedures return an int return code. The return code is 0 when successful or a non-zero HRESULT when an error occurs. An HRESULT is an OLE error code of the hexadecimal form 0x800nnnnn, but when it is returned as an int value it has the form -214nnnnnnn.

For example, passing an invalid object name (for example, 'SQLOLE.Xyzzy') to sp_OACreate will cause it 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)) will result in an unreadable string, because each byte of the HRESULT is converted to a single ASCII character. You can also use the following sample stored procedure sp_hexadecimal to convert an int HRESULT to a char value that contains a readable hexadecimal string.

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(255),
    @hexvalue varchar(255) OUTPUT
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,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 @i = @i + 1
END
SELECT @hexvalue = @charvalue
  

You can use the following sample stored procedure sp_displayoaerrorinfo to display OLE Automation error information when one of these procedures returns a non-zero HRESULT return code. This sample stored procedure uses the sp_hexadecimal sample stored procedure.

CREATE PROCEDURE sp_displayoaerrorinfo
    @object int,
    @hresult int
AS
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @hresult, @hrhex OUT
SELECT @output = '  HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
    SELECT @output = '  Source: ' + @source
    PRINT @output
    SELECT @output = '  Description: ' + @description
    PRINT @output
END
ELSE
BEGIN
    PRINT "  sp_OAGetErrorInfo failed."
    RETURN
END