Ouput an Error from SP
-
I have an sp called 'spDTS' that executes a DTS package. Within this sp, another one (sp_displaypkgerrors) is called which does the error checking. I am calling spDTS from an ASP page. I need to alert the user if the package fails. Here's some of the code from spDTS:
CREATE proc dbo.spBATCH_FILE_DTS
@COMPANY_ID VARCHAR (30),
@USER_NAME VARCHAR (30) ,
@ERROR INT OUTPUT --USED FOR ERROR CODE
--Bunch of code in between, and then this:
EXEC @ERROR = sp_displaypkgerrors @object, 0
--------------------------------------------------------
Here's sp_displaypkgerrors. I have forced an error to ocurr which then
executes the code labeled with '*********'
CREATE proc sp_displaypkgerrors
@pkg as int ,
@error as int output
as
declare @numsteps int
declare @steps int
declare @step int
declare @stepresult int
declare @pkgresult int
declare @hr int
select @pkgresult = 0
EXEC @hr = sp_OAGetProperty @pkg, 'Steps', @steps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get steps'
EXEC sp_displayoaerrorinfo @pkg --, @hr
RETURN
END
EXEC @hr = sp_OAGetProperty @steps, 'Count', @numsteps OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get number of steps'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END
while @numsteps > 0
Begin
EXEC @hr = sp_OAGetProperty @steps, 'Item', @step OUTPUT, @numsteps
IF @hr <> 0
BEGIN
print 'Unable to get step'
EXEC sp_displayoaerrorinfo @steps --, @hr
RETURN
END
EXEC @hr = sp_OAGetProperty @step, 'ExecutionResult', @stepresult OUTPUT
IF @hr <> 0
BEGIN
print 'Unable to get ExecutionResult'
EXEC sp_displayoaerrorinfo @step --, @hr
RETURN
END
select @numsteps = @numsteps - 1
select @pkgresult = @pkgresult + @stepresult
end
--*************----
--HERE'S WHERE I WANT TO RETURN @ERROR BACK TO SP_DTS:
--*************----
IF (@pkgresult > 0) BEGIN
SET @ERROR = @pkgresult
RETURN @ERROR
--SELECT @pkgresult
-- PRINT 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)'
END
ELSE
BEGIN
print 'Packge Succeeded'
END
RETURN @ERROR
GO
-------------------------------------------------------
when I execute spDTS (using query analyzer), @ERROR is not returned. What am I doing wrong? If
I uncomment PRINT 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)',
this message is displayed in qa.
-
Any chance you forgot to call it with the OUTPUT specifier in the caller?
I mean
exec displaypkgerrors @pkg, @error
will appear to work, but the variable @error won't be returned
whereas
exec displaypkgerrors @pkg, @error OUTPUT
will (should) return the variable
-
I've been grappling w/this for several days now. I think your suggestion is the solution. Thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply