Bulk Insert VARA.SQLI into SQL Server table

[Mylene Chalut]

AUTOMIC VERSION 12.3.1

Hi guys,

I have a VARA.SQLI that retrieves information from OA database (about 16 rows).  I want to load that information into an already existing SQL table (2016).  I can't seems to make it work using a simple query in a JOBS.SQL job.  Somebody can point me some documentation on how I can do that please ?

Thank you in advance for your help :-)

Mylene

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[kay.koll]

Hi Mylene,
have you configured the SQLI permission in the UC_SYSTEM_SETTINGS
https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#AWA/Variables/UC_SYSTEM_SETTINGS/UC_SYSTEM_SQL_Variables_Parameters.htm#SQLVARInt

and more important the Security Level
https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#AWA/Variables/UC_SYSTEM_SETTINGS/UC_SYSTEM_VAR_SECURITY_LEVEL.htm

SQLI works on my 12.3.3 fine
Regards
Kay

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Christoph Rekers]

Hi @Mylene Chalut,

Did I get it right: you want to do the same select you used in the VARA.SQLI in an JOBS.SQL?​ This should work. The only difference is that you need to use a LOGIN or CONN object to connect to the Automic-DB. Once the select works properly within your JOBS you need to prep_process_report the JOBS's report (in JOBS's Post Process tab), pass all line to a second JOBS that does the update statements. Something like:

:SET &HND# = PREP_PROCESS_REPORT(,,"REP", "**")
:PROCESS &HND#
:pSET &LINE# = GET_PROCESS_LINE(&HND#)
:SET &RUNID= ACTIVATE_UC_OBJECT(MY_SQL_UPDATE_JOBS,,,,,PASS_VALUES,,)
:PRINT &LINE#
:ENDPROCESS
:CLOSE_PROCESS &HND#

Some may say: wait, there's a more elegant way to do that (with only one JOBS). But this solution is the one that's quickly done and easy to explain...

Cheers
Christoph 







------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------

[Mylene Chalut]

Yep !!  That works !!

Thank you :-)

------------------------------
Programmer-analyst
Statistics Canada
------------------------------