Automic Workload Automation

First, I apologize if this question has been asked before.  I am running version 9 of the Automation Engine.  I have a SQL Agent set up to run SQL jobs.  I have several stored procedures I would like to call, but I can't seem to get them to work.  I followed the steps in the documentation, with no luck.
This is the code in my process tab:
SQL_SET_STATEMENT_TERMINATOR TERM='!';
DECLARE @result int;
EXEC @result = SPUploadExternalData 'QCBT_Contacts',NULL,'1','1','1','1','0','1';
select @result;!

The job immediately errors out with nothing written to the report.  I have triple checked the security roles and the login has rights to execute the stored procedure.  I can run SQL statements with no problems, I just can't seem to get stored procedures to execute.  Thank you.

When we do this for Oracle stored procedures, we use this code;

sql_set_statement_terminator term="@";
begin
   dbms_output.enable(NULL);
   &SP_NAME#(&PARAMETER_LIST#);
end;

I haven't done this for SQL Server though...  We have been invoking them by calling sqlcmd from a windows agent.

Thank you Pete.  I was able to get this to work by creating a windows job and using SQLCMD.  I hate to say it, but I am a bit disappointed that the SQLJOB can't seem to handle executing a stored procedure. 

Thanks again for the help!

Perhaps you should open this as an issue with Support.  Either it's (not) working as designed and if so I should be fixed or the documentation updated to identify the restriction.

I'm betting that it can, we just haven't figured it out.  I just ran a small test where I executed a select, an exec, and another select without any of the terminator override stuff, and it seemed to work?  I did not try to assign a result to a variable however.  I just let the database agent check it.  The database agent seems to immediately terminate the script and sets the return code to 1 when an error occurs.

I'm interested because I've been meaning to get SQLCMD out of our process...

I've done some further testing with vanilla exec commands and am having success.  So I don't see any problem with doing it this way against SQLServer. The confusing part is that it is simpler than Oracles requirements!

I have tried to get it to work with just a simple Exec statement - fails immediately.

Like this: EXEC SPUploadExternalData 'QCBT_Contacts',NULL,'1','1','1','1','0','1';

What was your error message?  Does it give any clues?  In my case I used no delimiters around my parameter list and I only had one parameter.  This is what I tested with and it worked fine;

exec eis_staging.dbo.bp_call_job bj_create_weekly_event;

Old post but I am also having this issue.

I can run the predefined commands as select and list tables but not exec or desc.

Any idea?

Hi Lucas_Amorim_9853  - are you running against MSSQL or Oracle? It would help if you can post the error that you're getting, and the code/script you're running on your SQL job.

Also, make sure that your syntax is correct and there's no error on your stored procedure by running it on SQL Management Studio (MSSQL) or Toad/SQLNavigator (Oracle) first.

HiChristine Chavez,

Thanks for reply :)
I am running against Oracle.
The steps I am taking are: New Object > JOBS > SQL > and running the commands from the "Process" tab.
I've also tried from the "Form" using the "User defined" option.
There are no errors on report. The job simply ends with error: ....aborted (return code='0000000001').

I can run a "Select * from Dual" and it works fine.
But if I try for example: "DESC GMF_GB_REPORTING.PKG_DM_RESPONSE_INCRMNTL" the job ends.

The Oracle team is able to run using my account:

******************

SQL> desc GMF_GB_REPORTING.PKG_DM_RESPONSE_INCRMNTL

 

PROCEDURE SP_DMRESPONSE_SCORE_POINT

 

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ -------- ........

****************

I'm just wondering if the job is limited to basic commands?

 

Hi Lucas_Amorim_9853

I'm not sure if it's limited to basic commands. It seem that the SQL job for Oracle doesn't accept EXEC as well, among other things (ALTER TABLE, WHENEVER SQLERROR..). It throws the ORA error "invalid SQL statement". 

You may want to check the option "Agent log" in the 'SQL' tab >' Optional Reports' of your SQL job. You'll see the error here when you run your job.

I started a discussion to see if anybody has a list of what commands can/can't be processed by the Oracle SQL job:
https://community.automic.com/discussion/6929/what-are-the-supported-oracle-sql-commands-that-you-can-use-in-an-sql-job

So far, no response :)

I'm on extended vacation right now, so I can't look at exactly what I did.  But I think I had to alter the Oracle command delimiter to "@" and wrap my code in a BEGIN/END block to be able to EXEC a stored procedure?

Hi petwir
 @ or / should work (I was able to run my stored procedures using / as delimiter). When I inserted something through the Form tab it did change my delimiter from '/' to '@'. 

Lucas_Amorim_9853
You may want to check back on https://community.automic.com/discussion/6929/what-are-the-supported-oracle-sql-commands-that-you-can-use-in-an-sql-job You can get the columns of a table through Form tab > Insert line at current position > SQL > Automation Engine Commands > Get columns of a table. 

Regards,
Christine

Hi,
I am also trying to run an oracle script but I am getting this error:
 The database driver returned the following error message:
 java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

The Host on the attributes tab is different from the server that I am trying to connect to, but I have done that before with SQL jobs and they work.

I had forgotten to change "type=ORACLE" on the host's INI file, but when I changed it and restarted the agent, I get a similar error:
The database driver returned the following error message:
java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

I am using AE V10 and this is what I am trying to run
SQL_SET_STATEMENT_TERMINATOR TERM='@';
begin
 dbms_output.enable(NULL);
 xxunf_conversion_adhoc_scripts.uc4_test();
end;
@

JGi604607 - Do you have the correct Oracle ojdbc in the JDBC folder of the Agent?

No, I don't. I don't even have the JDBC folder on the Agent. :|

Step 3 on this guide should be all you need:

https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/10.0/All%20Guides/help.htm#ucackf.htm

That worked! Thank you for your help Michael_Pirson