Technical Article

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