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

    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 15:39.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    EXECUTE msdb.dbo.sp_add_job
    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    32
    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 15:01.

  4. #4
    Join Date
    Jul 2004
    Posts
    32

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

    )

    VALUES
    (
    ....)

    SELECT
    @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 08: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
  •