------------------------------------------------------------------------------------------
-- 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

EXEC spExecutePKGGlobalVariables @Server='SERVER02', 
  @PkgName='dtsTest1', @IntSecurity=1