How to execute a SQL Server stored procedure from a SQL job?

Stephanie MetzgerStephanie Metzger Database AdministratorPosts: 15 Journeyman ✭✭✭
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.

Comments

  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 1,102 Innovator ✭✭✭✭✭✭
    edited May 2015
    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.
    Pete
    AE 11.2.1/Windows2012R2/SQLServer12.0
  • Stephanie MetzgerStephanie Metzger Database Administrator Posts: 15 Journeyman ✭✭✭
    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!
  • Mark HadlerMark Hadler Posts: 635 Innovator ✭✭✭✭✭✭
    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.
  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 1,102 Innovator ✭✭✭✭✭✭
    edited May 2015

    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...

    Pete
    AE 11.2.1/Windows2012R2/SQLServer12.0
  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 1,102 Innovator ✭✭✭✭✭✭
    edited May 2015
    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!
    Pete
    AE 11.2.1/Windows2012R2/SQLServer12.0
  • Stephanie MetzgerStephanie Metzger Database Administrator Posts: 15 Journeyman ✭✭✭
    edited May 2015
    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';
  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 1,102 Innovator ✭✭✭✭✭✭
    edited May 2015
    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;
    Pete
    AE 11.2.1/Windows2012R2/SQLServer12.0
  • Lucas AmorimLucas Amorim Posts: 45 Journeyman ✭✭✭

    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?

  • Christine ChavezChristine Chavez USTeam Automic Posts: 222 Specialist ✭✭✭✭
    Hi @Lucas Amorim  - 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.

  • Lucas AmorimLucas Amorim Posts: 45 Journeyman ✭✭✭

    Hi Christine 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?


  • Christine ChavezChristine Chavez USTeam Automic Posts: 222 Specialist ✭✭✭✭
    edited August 2016
    Hi @Lucas Amorim

    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 :)


  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 1,102 Innovator ✭✭✭✭✭✭
    edited August 2016
    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?
    Pete
    AE 11.2.1/Windows2012R2/SQLServer12.0
  • Christine ChavezChristine Chavez USTeam Automic Posts: 222 Specialist ✭✭✭✭
    Hi @Pete Wirfs
     @ 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
    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
  • Judith GitahiJudith Gitahi Posts: 151 Journeyman ✭✭✭
    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;






  • Michael PirsonMichael Pirson Posts: 21 Journeyman ✭✭✭
    @Judith Gitahi - Do you have the correct Oracle ojdbc in the JDBC folder of the Agent?


  • Judith GitahiJudith Gitahi Posts: 151 Journeyman ✭✭✭
    No, I don't. I don't even have the JDBC folder on the Agent. :|
  • Judith GitahiJudith Gitahi Posts: 151 Journeyman ✭✭✭
    That worked! Thank you for your help @Michael Pirson
Sign In or Register to comment.