Stored procedure cant catch error state

yinhuigood · Post by **yinhuigood** » 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:

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:

When I run the Sp by sql server client, it returns:

Code: Select all

 
declare @int int
declare @rcode int
exec @rcode = dbo.sp_output @int out
select ltrim(@rcode) + ','+ ltrim(@int)

Result
-6,121

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.

chulett · Post by **chulett** » Sun May 27, 2012 2:44 pm

First suggestion would be to make sure that you are current on your fix packs. If you are then I'd suggest contacting your official support provider as it doesn't sound like you're doing anything wrong.

yinhuigood · Post by **yinhuigood** » Sun May 27, 2012 6:34 pm

Thanks for you help!
I didn't fix any packs to DS8.1. What packs do I need to fix? Thanks.
I'm not good at Sql server SP. Is there any problem about the SP code?

chulett · Post by **chulett** » Sun May 27, 2012 6:57 pm

You need all of them.

I don't do SQL Server, hopefully someone else can double-check that. The concept is sound, all you should have to do is raise the error... at least that was my experience with Oracle procedures.

ray.wurlod · Post by **ray.wurlod** » Sun May 27, 2012 7:55 pm

Fix Pack 4 should be a roll-up of everything previous. Check the readme file.

yinhuigood · Post by **yinhuigood** » Mon May 28, 2012 2:42 am

To ray.wurlod ,
There are 2 questions about Fix Pack 4.
1. I can't find the FP4 for at IBM web. What I've found is FP1,FP2 for DS8.1. Could you offer me the web? Thanks.
2. Are you sure that the problem could be resolved by FP4? If I change the DS8.1 to DS8.5, whether the problem is exists? Thanks.

In addition, I'll find some information about how to catch execption in sql server sp. Maybe it'll get effect.

chulett · Post by **chulett** » Mon May 28, 2012 7:45 am

All of those questions could be easily answered by your official support provider...

ray.wurlod · Post by **ray.wurlod** » Mon May 28, 2012 4:29 pm

You can also consult the readme files at Fix Central, as well as the "what's new in this release" documents at Information Center.