Report DTS Error Code and Description

if exists (select * from sysobjects where id = object_id('dbo.usp_GetDTSErrorMsg') and sysstat & 0xf = 4)
	drop procedure dbo.usp_GetDTSErrorMsg
GO

CREATE PROCEDURE dbo.usp_GetDTSErrorMsg 
/*************************************************************************
--FILENAME: 
--	archives\Datamart\Dev\SProcs\dbo.usp_GetDTSErrorMsg.sql
--CURRENT VERSION:
--	Revision:   1.0  
--	Date:   Oct 18 2002 
--SQL SERVER OBJECT NAME: 
--	dbo.usp_GetDTSErrorMsg.sql
--PURPOSE:
--	Error codes and messages from DTS package need to be logged. Complete
--	error information is only logged to msdb DTS logging tables. 
--ACTIONS:
--	Accept parameters for package and step to retrieve error information
--	retrieve execution details from msdb tables
--	Calculate hex error code from signed integer value
--	return hex error code and error description to caller
--INPUTS:
--	@vcrPkgName - sysname - name of package to return information for
--	@vcrStpName - sysname - name of step to return information for
--	@dtmStpStrt - datetime - the time the step started
--OUTPUTS:
--	@intErr via RETURN - non-zero value indicates failure
--	@vcrStpErrDesc varchar(500) -- error description text
--	@chrHrHex char(10) -- OA Hex Error result
*************************************************************************/
	-- Passed Parameters
	@vcrPkgName varchar(128)
	, @vcrStpName varchar(128)
	, @dtmStpStrt datetime
AS
BEGIN -- procedure
	-- Set connection defaults
	SET NOCOUNT ON
	SET DATEFORMAT dmy

	-- Declare local variables
	DECLARE @intErr int -- Error number
	DECLARE @vcrMsg varchar(500) -- general purpose message string
	DECLARE @vcrProcName sysname -- stored procedure name
	DECLARE @intStpErrCode int -- error code
	DECLARE @vcrStpErrDesc varchar(500) -- error description
	DECLARE @chrHrHex char(10) -- OA Hex Error result

	-- Initialise local variables
	SET @intErr = 0
	SET @vcrMsg = ''
	SET @vcrProcName = OBJECT_NAME(@@PROCID)
	SET @intStpErrCode = 0
	SET @vcrStpErrDesc = ''
	SET @chrHrHex = ''

	-- get step execution status
	-- Step start time is used to differentiate between log entries for same step
	SELECT 
		@intStpErrCode = Stp.errorcode
		, @vcrStpErrDesc = Stp.errordescription
	FROM msdb.dbo.sysdtspackagelog Pkg
		INNER JOIN msdb.dbo.sysdtssteplog Stp
			ON Pkg.lineagefull = Stp.lineagefull
	WHERE Pkg.name = @vcrPkgName
		AND Stp.stepname = @vcrStpName
		AND Stp.starttime = @dtmStpStrt

	SELECT @intErr = @@ERROR

	IF @intErr = 0
	BEGIN -- no error and one row returned
		-- convert error code to hex
		SELECT @chrHrHex = dbo.udf_varBinary2Hex(@intStpErrCode)
		-- select error code and description to return to caller
		SELECT @chrHrHex as ErrCode, @vcrStpErrDesc as ErrDesc
	END -- no error and one row returned
	ELSE
	BEGIN -- error occurred
		SET @vcrMsg = ' - Could not retrieve step detail for'
		SET @vcrMsg = @vcrMsg + ' Package:' + @vcrPkgName
		SET @vcrMsg = @vcrMsg + ', Step:'+ @vcrStpName
	END -- error occurred

	-- if an error occured raise exception message
	IF @intErr <> 0
	BEGIN -- Error <> 0
		-- raise error exception
		SET @vcrMsg = @vcrProcName + ' - completion code ' + CAST(@intErr as varchar(20)) + @vcrMsg
		EXEC master.dbo.xp_logevent @vcrMsg, ERROR
	END -- Error <> 0
	
	-- return completion code to caller
	RETURN (@intErr)

END -- procedure

/****** Object: User Defined Function dbo.udf_varBinary2Hex Script Date: 3/10/2002 4:02:48 PM ******/
IF EXISTS (SELECT name FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.udf_varBinary2Hex') 
						AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.udf_varBinary2Hex
GO

CREATE FUNCTION dbo.udf_varBinary2Hex (
/*************************************************************************
--FILENAME: 
--	archives\Datamart\Dev\Functions\dbo.udf_varBinary2Hex.sql
--CURRENT VERSION:
--	Revision:   1.0 
--	Date:   Oct 18 2002 10:01:38 
--SQL SERVER OBJECT NAME: 
--	dbo.udf_varBinary2Hex.sql
--PURPOSE:
--	Convert binary value into Hex string.
--	Used for reporting error codes from OA stored procedures.
--ACTIONS:
--	Loop through binary input string one bit at time. Convert each bit into equivalent
--	hex code. Concatenate hex codes together to provide final string
--INPUTS:
--	@binValue - varbinary - Binary value to convert
--OUTPUTS:
--	@vcrHexValue varchar(255) - Hex string to return
*************************************************************************/	@binValue varbinary(255)
)
RETURNS varchar(255)
AS

BEGIN
	DECLARE @intLoop int -- loop counter
	DECLARE @intParmLen int -- length of passed value
	DECLARE @chrHexStr char(16) -- constant string of Hex characters

	DECLARE @intSingleByte int -- single byte from binary value
	DECLARE @intFirstBit int -- first bit of binary value
	DECLARE @intSecondBit int -- second bit of binary value
	DECLARE @vcrHexValue varchar(255) -- Hex string that is returned
	
	-- initialise variables
	SELECT @vcrHexValue = '0x'
	SELECT @intLoop = 1
	SELECT @intParmLen = DATALENGTH ( @binValue )
	SELECT @chrHexStr = '0123456789ABCDEF'

	WHILE ( @intLoop <= @intParmLen )
	BEGIN
		-- reinitialise pointers
		SET @intSingleByte = 0
		SET @intFirstBit = 0
		SET @intSecondBit = 0
		-- get bit pointers from binary value
		SET @intSingleByte = CONVERT ( int, SUBSTRING ( @binValue, @intLoop, 1 ) )
		-- get base16 number
		SET @intFirstBit = FLOOR ( @intSingleByte / 16 )
		SET @intSecondBit = @intSingleByte - ( @intFirstBit * 16 )
		-- concatenate Hex strings values based on bit value
		SET @vcrHexValue = @vcrHexValue + SUBSTRING ( @chrHexStr, @intFirstBit + 1, 1 ) 
		SET @vcrHexValue = @vcrHexValue + SUBSTRING ( @chrHexStr, @intSecondBit + 1, 1 )

		-- increment loop counter
		SET @intLoop = @intLoop + 1
	END
	
	RETURN ( @vcrHexValue )

END
GO