none
How to create a scheduled job on SQL Server 2005 using T-SQL?

    Question

  • Hi Guys,

     

    Any assistance you people can provide will be considered grateful.   I’m in search of a generic cookie cutter snippet of code that simulates the creation of an Oracle DBMS_JOB, but I need it for SQL Server 2005.   

     

    Beneath, highlighted in red, is how we create a DBMS_JOB in Oracle, but I need the snippet of code to accomplish the same objective on SQL Server 2005.  In Oracle, we deploy our scheduled jobs using anonymous pl/sql which is written in black, as shown beneath.    To facilitate conversion of our project scheduled task it would be great if someone can assist me in translating the code beneath written in black and red.

     

    REM spool schedule-jobs_a.sql.log

    REM declare

    REM     jobno NUMBER;

    REM cursor job_cur is select job from user_jobs

    REM where what like 'dbms_utility.analyze_schema%';

    REM begin

    REM

    REM for job_rec in job_cur loop

    REM     dbms_job.remove(job_rec.job);

    REM

    REM end loop;

    REM    dbms_job.submit(jobno,what => 'dbms_utility.analyze_schema(''&&anal'',''compute'');', interval => 'TO_DATE(SUBSTR(TO_CHAR(TRUNC(SYSDATE+1)),1,10)) + 360/1440', next_date => SYSDATE);

    REM    dbms_output.put_line( 'Next job '||jobno );

    REM exception

    REM   when others then raise;

    REM end;

    REM /

     

     

    PS, I’m not looking for instructions to create a Scheduled Job through Microsoft SQL Server Management Studio, but rather, I need T-SQL code to create a Schedule Job to run daily at some period of time and run some snippet of code.

     

    Thanks,

    Vikram

     

    Thursday, November 08, 2007 6:45 PM

Answers

  • Refer the below stored procedure in books online to create jobs via T-SQL

     

    sp_add_job - dds a new job executed by the SQLServerAgent service.

    sp_add_jobstep - Adds a step (operation) to a job.

    sp_add_jobstep - Creates a schedule for a job. sp_add_jobschedule is provided for backward compatibility only.

    sp_add_jobserver - Targets the specified job at the specified server.

     

    Friday, November 09, 2007 12:05 AM
    Moderator

All replies

  • Refer the below stored procedure in books online to create jobs via T-SQL

     

    sp_add_job - dds a new job executed by the SQLServerAgent service.

    sp_add_jobstep - Adds a step (operation) to a job.

    sp_add_jobstep - Creates a schedule for a job. sp_add_jobschedule is provided for backward compatibility only.

    sp_add_jobserver - Targets the specified job at the specified server.

     

    Friday, November 09, 2007 12:05 AM
    Moderator
  • Just to add what Viday has mentined, all these stored procedures are in MSDB Database.

     

    Madhu

    Friday, November 09, 2007 1:51 AM
    Moderator