stored procedure stage always returm 0
Moderators: chulett, rschirm, roy
stored procedure stage always returm 0
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.
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.
Regards,
Sajid KP
Sajid KP
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..
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..
Regards,
Sajid KP
Sajid KP
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.
Or I guess wait and see if anyone else has any other thoughts.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
see http://articles.techrepublic.com.com/51 ... 45786.html
Looks like it is the RAISERROR in SQL Server.
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!
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!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am