Require help for Automic automation

[Chua Ricky]

Hi , i need some help please.

I am running a stored procedure from my server and executing it via Automic. Basically this stored procedure checks for certain validations and it will return 1 if there is error and 0 if it is successful.

However, when i trigger it in Automic, it is always showing success in Automic. Is there a way i can programme it to be ENDED_NOT_OK if the stored procedure returns 1?

-------------------------------------------

[Kumar Rahul]

You can use prep_process_report in the post process tab of the job and filter the keyword 1, and modify the return code to anything other than 0. For example, 

:SET &HND# = PREP_PROCESS_REPORT(,,,"*1*")
:PROCESS &HND#
:   MODIFY_STATE RETCODE=1
:ENDPROCESS

For more details please follow the documentation:- https://docs.automic.com/documentation/webhelp/english/AA/21.0/DOCU/21.0.14/Automic%20Automation%20Guides/Content/Script/Reference/PREP_PROCESS_REPORT.htm?Highlight=prep_process_report

-------------------------------------------

[Daryl Brown]

How are you invoking the stored proc, exactly?  Are you sure you're capturing the return code properly?

You may find this link helpful: https://stackoverflow.com/questions/4158796/how-do-i-capture-a-returned-value-from-a-stored-procedure-using-windows-command

[Chua Ricky]

Hi , i am invoking it through the SQLCMD command

sqlcmd -S [MyserverName] -E -Q "DECLARE @return_value INT; EXEC @return_value = [My Stored Proc]; SELECT 'Return Code' = @return_value;"

This is the output from Automic:

-------------------------------------------

[Nicole Boeger]

We use an OS-Agent and start scripts via SQLPLUS on databases. This way we have the full functionality of sql scripts for error handlings.

------------------------------
Regards, Nicole
------------------------------