Post reply

Running Jobs from Command Prompt

  • September 1, 2008 at 10:32 pm

    #382474

    Hi

    I would really need some help with running jobs from the Command Prompt have the following jobs in SQL :ermm:

    1. Job Name: PREPROD_ORA_POPULATE

    Steps : 1 - VIEW_GENERATION

    2 - TABLE_POPULATE

    2. Job Name: PREPROD_UPDATE_COUNT

    Steps : 1 - TABLE_UPDATE

    2 - DATA_COUNT

    3. Job Name: PREPROD_ERROR_GENERATE

    Step : 1 - EXCEL_GENERATE

    I would like to create a batch file which should execute the above steps in order. So once Job1 finishes ..execute Job2 when Job 2 finishes, Execute Job3 .

    Thanks

    Liju

  • September 2, 2008 at 6:19 am

    #865930

    Could someone please help me out.

    Help very much needed

  • September 2, 2008 at 6:51 am

    #865948

    Use the SQLCMD program:

    sqlcmd.exe

    [{ { -U login_id [ -P password ] } | –E }]

    [-S server_name [ \ instance_name ] ]

    -Q"execute msdb.dbo.sp_startjob @job_name = 'PREPROD_ORA_POPULATE'"

    and any other parameters that are needed.

    Note that sp_startjob is an asynchronous command - that is, after the job is started, control returns back to the next SQL statement. Return of control does not wait for the job to finish.

    SQL = Scarcely Qualifies as a Language

  • September 2, 2008 at 6:52 am

    #865949

    The SQL Job Agent is designed to execute asynchronously. Rather than fight this with something in a batch file that waits for the status of a job to finish, I suggest you modify your jobs a bit.

    Add a step to job 1 that calls sp_start_job to start job 2. Then do the same to start job 3.

    From a command prompt, you will then be able to use SQLCMD to simply call sp_start_job on job 1 and all three of your jobs will execute in sequence. Your command prompt will return as soon as job 1 starts.

  • September 3, 2008 at 11:17 am

    #866667

    I think in this instance, I would create an SSIS package that does what you want. Then call the package on the command line.

    Or I would simply use SQLCMD statements for each step within a batch file. Checking for errors as you go.

    Either of these methods gives you more control than starting a sql agent job as they are able to check for errors on each statement. The benefit of the package is that it also allows for parallel processing if you want to use it.

    Gary Johnson
    Sr Database Engineer

  • October 13, 2008 at 3:05 pm

    #884336

    Attempting to do the same. Since the sproc (sp_start_job) is asynchronous (fire and forget) how do you check errors along the way?

    I am running the following a .bat file with the following:

    echo off

    set path=C:\Program Files\Microsoft SQL Server\90\tools\binn\;%PATH%

    echo Starting MY ETL > my.log

    sqlcmd.exe -S "myserver" -Q "execute msdb.dbo.sp_start_job @job_name = 'myjob' " >> my.log

    :TheEnd

    *tc

    - Tony C

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply