Binaryworld - How to display COM error description in SQL Stored procedure ? ... [ SQL Server -> Stored Procedure ]

When we deal with COM object using sp_OAxxxx stored procedure sometimes you need exact error description to Identify the problem. Here is the solution for this type of problem. sp_displayoaerrorinfo stored procedure will display any error encountered while executing sp_OAxxxx stored procedure.

Click here to copy the following block

-----------------------------------------------------------------------
-- To Convert com Error to nore readable Hex Number
-----------------------------------------------------------------------

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

GO

-----------------------------------------------------------------------
-- To Display COM Error
-----------------------------------------------------------------------

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

GO

-----------------------------------------------------------------------
-- Test sp_displayoaerrorinfo stored procedure by generating some error
-----------------------------------------------------------------------
create proc usp_Test
As
BEGIN
DECLARE @hr integer
DECLARE @obj integer
DECLARE @results bit

--Try to create CDO object
--If CDONT Library is not installed it will throw an error
EXEC @hr = sp_OACreate 'CDO.Message', @obj OUTPUT
--EXEC @hr = sp_OACreate 'CDO1111.Message', @obj OUTPUT

IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @obj,@hr
END
Else
Print 'CDO Lib is installed on your system'

EXEC @hr = sp_OADestroy @obj
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @obj,@hr
END
END
GO

Exec usp_Test