Question

How to pass and retrieve the result from SQL Stored Prodecure in AA360

  • 21 March 2024
  • 3 replies
  • 69 views

Badge +1

If anyone has experience with using stored procedures, please assist me. They save me a lot of time writing SQL queries on the Automation Anywhere command line dashboard. I've successfully created Stored procedure in sql database, and they return the correct results. However, when attempting to run them in Automation Anywhere, the results are not as expected. Is there a specific way or syntax for using SQL in Automation Anywhere? If so, could you please provide a snippet to help me understand? Thank you in advance.


3 replies

Userlevel 1
Badge +4

Hi Raghuvir,

Please check the given below link that will be helpful:-

https://docs.automationanywhere.com/bundle/enterprise-v2019/page/enterprise-cloud/topics/aae-client/bot-creator/commands/cloud-using-database-run-sp-action.html 

https://apeople.automationanywhere.com/s/article/How-to-pass-parameters-using-Run-Store-Procedure-package-in-the-Automation-360

 
Badge +1

I’m not a guru with AA, yet, but this is what I have standardized on. 

First, obviously is the Database Connect, setting up the JDBC correctly.

Next, when I code for the stored procedures, all parameters are accounted for whether or not they are needed. For example, I am looking at a stored procedure that can take 8 optional parameters. I only need the first and last one. But I code all 8 in the proper sequence. I ran into issues if I did not code them all.


Next, I have standardized on always writing the output to a csv file, and then read the csv into a table. That way I have copy of the results for audit purposes. If I get the results to a data table and the bot fails, I have nothing.

My standard process, overall, looks like this:

 

I hope this helps you. And I am open to any critique of others reading this.

dvn

Badge +1

Thank you so much for taking the time to address my query so thoroughly amidst your busy schedule. I initially thought it might go unanswered, but you provided such a helpful response. I truly appreciate it. Additionally, I received some solution suggestions and links to KB articles from Automation Anywhere. I'll explore those options along with your approach to see if my issue can be resolved. Thanks once again for your assistance.

Reply