Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: Scheduling a job using a stored procedure

    I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time.

    I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?
    Last edited by gaj; 07-01-04 at 14:39.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    EXECUTE msdb.dbo.sp_add_job

  3. #3
    Join Date
    Jul 2004
    The code snippet below has completed successfully, but i there seems to be a problem with it, can you use a local variable as a value for job name like below?

    exec msdb.dbo.sp_add_job @job_name = @AuctionID
    exec msdb.dbo.sp_add_jobstep @job_name = @AuctionID,
    @stepname = 'Step1',
    @command = 'Update Auction
    SET AuctionState = ''closed''
    WHERE AuctionID = @AuctionID'

    thanks for the earlier response.
    Last edited by gaj; 07-02-04 at 14:01.

  4. #4
    Join Date
    Jul 2004

    Problem scheduling this job

    Can anyone see a problem with the code below, STEP1 doesn't get included when the job is scheduled. Suspected errors highlighted in red. The errors haven't been added cos they are confusing.
    INSERT INTO Auction



    @AuctionID = @@Identity
    declare @Aid varchar
    set @Aid = convert(varchar,@auctionID,15)
    Declare @sqlcommand varchar(255)
    Set @sqlcommand = 'UPDATE Auction SET AuctionState = ''closed'' WHERE AuctionID = ' + @AID

    exec msdb.dbo.sp_add_job @job_name = @AUCTIONID
    exec msdb.dbo.sp_add_jobstep @job_name = '@AUCTIONID',
    @step_name = 'Step1',
    @command = @sqlcommand,
    @database_name = 'Auction',
    @server = 'XX'
    exec msdb.dbo.sp_add_jobschedule @job_name = @AuctionID,
    @name = 'AuctionsScheduled',
    @freq_type = 1,
    @active_start_time ='120000',
    @active_start_date = '20040703',
    @freq_recurrence_factor = 1
    exec msdb.dbo.sp_add_jobserver @job_name = @auctionID,
    @server_name = 'XX'

    Last edited by gaj; 07-03-04 at 07:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts