Retrieving errors in DTS steps
-
Hi,
I need to retrieve the exact error message against each step in a DTS Package. I am executing the package from a T-SQL Stored Procedure using the techinque you have described.
This is the code snippet I need help with:
SET @pbstrSource_ = ''
SET @pbstrDescription_ = ''
SET @pbstrHelpFile_ = ''
SET @pbstrIDofInterfaceWithError_ = ''
SET @pErrorCode_ = 0
SET @pHelpContext_ = 0
EXEC sp_OAMethod @Step, 'GetExecutionErrorInfo', NULL,
@pErrorCode_ OUT,
@pbstrSource_ OUT,
@pbstrDescription_ OUT,
@pbstrHelpFile_ OUT,
@pHelpContext_ OUT,
@pbstrIDofInterfaceWithError_ OUT
None of the parameters (@pErrorCode_, etc) return any value. In fact, when I set the @pErrorCode_ variable to 4 before the call to sp_OAMethod, and later printed its value, it had changed to 0.
This method is the only way to get detailed error messages on DTS package steps, yet I can't seem to use it from T-SQL. I did confirm that the method returns detailed error messages when called from a Visual Basic module, but I can't seem to get anything out of it in T-SQL. The downside of using VB is that then the wrapper module/object used to handle package execution needs, EITHER, to be invoked remotely on a machine that has Oracle client tools (since the package exports some data from a SQL Server database to some Oracle tables), OR, the Oracle client tools need to be installed on each client that needs to execute the package.
Regards,
Affan
-
This was removed by the editor as SPAM
-
Never had any success with GetExecutionErrorInfo. Ended up building my own script to extract the info from the DTS logging tables. You can have a look at my script here,
http://qa.sqlservercentral.com/scripts/contributions/556.asp
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
OH MY! I'm probably a bit late here, but I just came across this on my own today. This was for a package, but I think it applies for the step as well.(?) I believe the secret to what you're trying to do is to use the sp_OAGetErrorInfo sproc.
Here's what I came up with: (the refernced SP is listed below)
...
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** Method failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
...
(these SPs can be found in a variety of places. below is my tweaked version that seems to work right
):/****** 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
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
/*the following check just caused trouble for me:
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
):Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply