Stored procedure cant catch error state
Posted: Sun May 27, 2012 9:11 am
Hi, Masters,
My text conditions is:
OS:windows server 2003
DS:8.1
Database:Sql server 2005
Job flow: Stored Procedure --> Peek
Problem: I'm tring to catch Sp status by STP. But no matter what return value the sp was set, The job was finished and return valus was 0!
Some pelple told setting retrun valus by RAISERROR in sql server. But there wasn't any effect when I did it.
SP code:
Job log:
When I run the Sp by sql server client, it returns:
I've tried to set some values in STD Error Codes tab. But it didn't get effect.
Some people told me that: create a job(STD--> Sequential file) to save return value in txt, then create a sequence to judge job's status by check the return values in txt. I don't think that IBM couldn't fix the problem except the method. Can any master help me? Thanks.
My text conditions is:
OS:windows server 2003
DS:8.1
Database:Sql server 2005
Job flow: Stored Procedure --> Peek
Problem: I'm tring to catch Sp status by STP. But no matter what return value the sp was set, The job was finished and return valus was 0!
Some pelple told setting retrun valus by RAISERROR in sql server. But there wasn't any effect when I did it.
SP code:
Code: Select all
ALTER procedure [dbo].[sp_output]
@output int output
as
set @output = 121
RAISERROR ('test error.' , 16, 1) WITH SETERROR
RETURN
Job log:
Code: Select all
Stored_Procedure_0,0: Info: Untitled2.Stored_Procedure_0: [Microsoft][ODBC SQL Server Driver][SQL Server]test error.
Peek_1,0: ProcCode:0 tint:Code: Select all
declare @int int
declare @rcode int
exec @rcode = dbo.sp_output @int out
select ltrim(@rcode) + ','+ ltrim(@int)
Result
-6,121
Some people told me that: create a job(STD--> Sequential file) to save return value in txt, then create a sequence to judge job's status by check the return values in txt. I don't think that IBM couldn't fix the problem except the method. Can any master help me? Thanks.
