Automic Workload Automation

Hi, I am new to this forum (Disclosure  ) I have follow the instructions on this posting:

https://community.automic.com/discussion/comment/27187/#Comment_27187

 In my objects in the post Process Tab, I have
:SET &SQL_RESULT# = PREP_PROCESS_REPORT(,,,"BU*") :PROCESS &SQL_RESULT# :ENDPROCESS :SET &ACT_JOB#=ACTIVATE_UC_OBJECT('JOBS.UNIX.STAHFM.…MOVE.SQL.FILE',,,,,PASS_VALUES)

In the Job File I have in the Process Tab
cd\Inbox
echo &SQL_RESULT# > Functions.txt

As I go to my inbox, I see the newly created file, so the ACTIVATE_UC_OBJECT appears to properly call the Job File, for that is the only place where the echo command is written. The problem is that the file is empty. The SQL Query does produce the appropriate information, because before I went in this direction, I downloaded the file from the Reports>Directory>Download option. The file did contain the appropriate data. Two columns of data separated by a semicolon, For example:
BU_PHLEGY;PH51

Could anyone tell me where is the process failing or point me in the right direction?

Thanks

:SET &SQL_RESULT# = PREP_PROCESS_REPORT(,,,"BU*")
:PROCESS &SQL_RESULT#
:ENDPROCESS
:SET &ACT_JOB#=ACTIVATE_UC_OBJECT('JOBS.UNIX.STAHFM.MOVE.SQL.FILE',,,,,PASS_VALUES)

Your activation of the object is outside of the process loop. The loop runs through without doing anything and then you activate the object.

If you only expect one line of content you can do the activation outside of the loop after using GET_PROCESS_LINE to receive the pointers content:

:SET &SQL_RESULT# = PREP_PROCESS_REPORT(,,,"BU*")
:PROCESS &SQL_RESULT
:SET &CONTENT# = GET_PROCESS_LINE(&SQL_RESULT#)
:ENDPROCESS
:SET &ACT_JOB#=ACTIVATE_UC_OBJECT('JOBS.UNIX.STAHFM.MOVE.SQL.FILE',,,,,PASS_VALUES)

=> to make the PASS_VALUES work you must register the variable that must be available in the activated object as an "object variable". You should also be able to use :PUBLISH (just lookup the documentation).

Regards
Joel

Joel,
Thank you for responding. The query returns a few hundred lines, I need to capture all of them and place the output in a txt file. Based on your comment I will try the following(right now the services are down):
SET &SQL_RESULT# = PREP_PROCESS_REPORT(,,,"BU*")
:PROCESS &SQL_RESULT
:SET &ACT_JOB#=ACTIVATE_UC_OBJECT('JOBS.UNIX.STAHFM.MOVE.SQL.FILE',,,,,PASS_VALUES)
:ENDPROCE:error:

I will let you know the results ( I will read the Publish Doc also)

Hi Paul

Then you better don't work with ACTIVATE_UC_OBJECT as it will activate one object per line.

Can't you write the output into a file and do a follow-up job that does a file transfer?

Joel,
        You are right the intention is to write the output to a file and then move the whole file. Those are the instructions I followed in this post:
https://community.automic.com/discussion/comment/27187/#Comment_27187
However, it failed. 
Also, inserting the instruction ACTIVATE_UC_OBJECT within the loop did not work. 
Any ideas how to create the file? I know how to move it
Thanks 

Raul_Rodriguez_8894

here is a short example, possibly it helps in you case as well.....

https://community.automic.com/discussion/8776/sql-stored-procedure-return-values#latest

FrankMuffke
Thank you I will try the example

So I got the SQL query results in a file, using the following method:

1. I set up a VARA.SQL object
2. I set up a Script where I use PREP_PROCESS_VAR (This calls the variable above)
3. Then I use the WRITE_PROCESS to produce the file

This would be fine if I can get the two columns I am suppose to get but I am getting a one long string for example each row has BU_RAUL;CLG;BU_RAUL;CLEC;BU_RAUL;9XF
When I was expecting three different rows:
BU_RAUL;CLG
BU_RAUL;CLEC
BU_RAUL;9XF
This is how the query preview display is in the VARA.SQL object, which is correct.
So it begs the question, how do I make sure I get each record on each row rather than a random concatenation of rows?

Here is my code in the script:

Plan A would be using the delimiter Parameter in combination with &NL# (not tested myself)

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucacxi.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CData%2520Sequences%7C_____18

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaahi.htm%3FTocPath%3DAutomation%2520Engine%2520Script%2520Guide%7COrdered%2520by%2520Function%7CStrings%7C_____28

hope it helps & works

Plan B - use a WIN (or UNIX) Job like shown in my example (link above)

FrankMuffke
So It turns out it was just a visual issue, the file is process as if there are multiple lines(This is correct); If I opened the file with note pad it look like one long string. 
Since there is no issue here, I have moved to the next step.


:CLOSE_PROCESS &HND#


I want to use this same script 4 times the difference will be the SQL queries (for this I have created 4 VARA.SQL variables) , the names are:

1. VARA.SQL.BUSINES_UNITS
2. VARA.SQL.BUSINESS_FUNCTIONS
   
3. VARA.SQL.ICPLK
   
4. VARA.SQL.ICPPLG
   

This is to comply with my requirement of creating 4 different files, I don't know what to do next. How do I get it to run the 4 queries in sequence and create the files one after the next. I thought about a workflow, but I don't want to create 4 scripts and run one after the other (This will work but it looks ugly) . I could copy the script 4 times and hardcode the VARA.SQL names on the first step and this should work but again it look ugly.
Any help will be greatly appreciated.
Raul

There are a lot of possibilities that would blow up the frame of this topic.

I would put the code into an include and use the oldstring=newstring method to change the vara name
https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaadq.htm?Highlight=:INC

you can put the VARA names in an static VARA object and use a FOREACH Workflow to call the job/script with the include

or you use the script command prep_process_var (to process your static vara with the vara_names)
and within this process loop you perform the  PREP_PROCESS_VAR("VARA.SQL.BUSINESS_UNITS",,


have fun :-)