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