stored procedure stage always returm 0

sajidkp · Post by **sajidkp** » Wed Aug 19, 2009 6:04 am

Hi,

I am calling a stored proecdure in sql server database using datastage stored procedure stage.

SP stage ----> seq file

where the return code i am sending to a seq file. but when the procedure success then it return 0 and when it fails also the job finishes successfully and the return code is still 0 . How this is happening? please help me.

chulett · Post by **chulett** » Wed Aug 19, 2009 7:16 am

Define 'fails'. What does the proc do when there is a problem, does it raise an error or just log some error text?

sajidkp · Post by **sajidkp** » Wed Aug 19, 2009 7:23 am

when i exceute the proc through sql server client , it gives me an error with some error message. and the return code is -6.

same procedure when i run in DS using storedprocedure stage , it the job finish successfully and the return code is 0.

the error message that i am getting in the sql client is also coming in direcotr log , but it is niether a warning nor a fatal error . and job finishes successfully..

chulett · Post by **chulett** » Wed Aug 19, 2009 7:24 am

Have you tried to use the (don't recall exactly) the "error handling" tab in the stage to let it know that should be a fatal error?

chulett · Post by **chulett** » Wed Aug 19, 2009 7:26 am

(redacted double post)

sajidkp · Post by **sajidkp** » Wed Aug 19, 2009 7:26 am

I Have tried that also. I put all the error codes( -6 0 1 2 3 etc) still it doesnt make any difference. and the output colum(return code) i am getting 0 always!!!!

chulett · Post by **chulett** » Wed Aug 19, 2009 7:28 am

Zero isn't an error. What happens if you try it with just "-6" rather than a list like that?

sajidkp · Post by **sajidkp** » Wed Aug 19, 2009 7:31 am

first thing i have done was , tried with -6. it didnt work. i redirected the return value of procedure call to seq file , its getting value 0 always.

chulett · Post by **chulett** » Wed Aug 19, 2009 7:38 am

Then it seems to me you have a couple of choices. You can post the code so that someone who knows SQL Server (I don't) can perhaps spot an issue with the code or changes that may need to be made to support its use in DataStage. That or involve your official support provider.

Or I guess wait and see if anyone else has any other thoughts.

chulett · Post by **chulett** » Wed Aug 19, 2009 10:37 am

For whatever this is worth, I haven't seen this issue in Oracle as long as the proc properly does a RAISE of any errors encountered.

rcanaran · Post by **rcanaran** » Wed Aug 19, 2009 12:50 pm

see http://articles.techrepublic.com.com/51 ... 45786.html

Looks like it is the RAISERROR in SQL Server.

jdmiceli · Post by **jdmiceli** » Thu Sep 24, 2009 10:36 am

We had the same issue for a bit until I figured out the any use of the RETURN functionality of SQL Server returns a SUCCESS message to DataStage, regardless of what number you attach to it.

The only way I have found to beat this is to use RAISERROR, with a severity level greater than 10. This means you really only have 8 severity levels to work with since 1-10 are informational and act the same as RETURN and 19-25 are reserved for SYSADMIN role of SQL Server.

Hope that helps!

Sreenivasulu · Post by **Sreenivasulu** » Thu Sep 24, 2009 10:48 am

very good one ..
really usefull..

Regards
Sreeni