Report DTS Error Code and Description

,

This stored procedure extract the error code and description form the DTS package log tables in the msdb database.

You just pass it the package name, step name and the time that the step started ( because you could end up with many entries in the log ) and it will return the Error Code and the Error Description.

Sample of output,
ErrCode    ErrDesc                              
---------- -------------------------------------------------
0x8004043B Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80040e14): INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

I use this procedure in the on completion action for each step in a DTS package that does many parrallel data loads. Then I log the returned error and description to a table which is checked at the end of the package.

It uses a function called udf_varBinary2Hex, which is included. This is my conversion of the sp_hexadecimal procedure listed in the "OLE Automation Return Codes and Error Information" topic in books online.

Comment and questions welcome.

Phill

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

Rate

Share

Share

Rate