------------------------------------------------------------------------------------------
-- Filename    :  MS-OLE_SPs.sql
-- Description :  Creates the two "OLE Automation Return Codes and Error Information"
--                srored procedures referenced under Books On-Line
--                Please note this script has been produced directly from Microsoft SQL 
--                Server Books Online (v7). All rights Acknowledged. 
------------------------------------------------------------------------------------------

-- Downloaded from http://www.swynk.com/friends/green


/****** Object:  Stored Procedure dbo.sp_hexadecimal ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_hexadecimal]
GO

/****** Object:  Stored Procedure dbo.sp_displayoaerrorinfo ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_displayoaerrorinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_displayoaerrorinfo]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  Stored Procedure dbo.sp_hexadecimal ******/

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

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  Stored Procedure dbo.sp_displayoaerrorinfo ******/

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

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO