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 it 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 also use the following sample sp_hexadecimal stored procedure 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 sp_displayoaerrorinfo stored procedure to display OLE Automation error information when one of the OLE Automation procedures returns a nonzero HRESULT return code. This sample stored procedure uses sp_hexadecimal.

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

  

See Also:
sp_OAGetErrorInfo  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.