DSXchange

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.

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

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..

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?

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!!!!

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

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.

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.

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.

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

Looks like it is the RAISERROR in SQL Server.

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!

very good one ..
really usefull..

Regards
Sreeni