Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52

    Question Unanswered: jobschedule not enabled using sp_add_jobschedule

    I have a VB6 application from which I want to create and schedule a jobs. The individual jobs should run once and be deleted when succesfully finished. Almost everything works fine. But enabling the jobschedule won't work. In the sp below I set the @enabled parameter of sp_add_jobschedule to 1 (is also default) which should indicate that the schedule is enabled. How to fix this, or what I am doing wrong? I tried to place an execute sp_update_jobschedule at the end of the sp, but no effect at all.
    HTML Code:
    CREATE PROCEDURE mis_CreateCustomJob (
      @strUserName varchar(50),
      @strDateTimeStamp varchar(14),
      @strJobType varchar(10),
      @intDate int,
      @intTime int
    )
    
    AS
      BEGIN TRANSACTION            
        DECLARE @JobID BINARY(16)  
        DECLARE @JobName VARCHAR(67) 
        DECLARE @ReturnCode INT 
           SELECT @JobName = 'jb_' + @strUserName + @strDateTimeStamp
    
        SELECT @ReturnCode = 0
    
      BEGIN 
    
        -- Add the job
        DECLARE @strDescription VARCHAR(100)
        SELECT @strDescription = 'Job aangemaakt met MUC door ' + @strUserName
        EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
          @job_id = @JobID OUTPUT ,
          @job_name = @JobName, 
          @owner_login_name = N'Admin', 
          @description = @strDescription, 
          @category_name = N'[Uncategorized (Local)]', 
          @enabled = 1, 
          @notify_level_email = 0, 
          @notify_level_page = 0, 
          @notify_level_netsend = 0, 
          @notify_level_eventlog = 2, 
          @delete_level= 1
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
        -- Add the job steps
        DECLARE @strCommand varchar(100)
        SELECT @strCommand = 'SET ANSI_NULLS ON' + char(13) + 'SET ANSI_WARNINGS ON' + char(13) + 'go ' + char(13) + char(13) + 'exec mis_JobTest'
        EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
          @job_id = @JobID, 
          @step_id = 1, 
          @step_name = N'Upload Contracttabellen',
          @command = @strCommand,
          @database_name = N'mis', 
          @server = N'', 
          @database_user_name = N'', 
          @subsystem = N'TSQL', 
          @cmdexec_success_code = 0, 
          @flags = 0, 
          @retry_attempts = 0, 
          @retry_interval = 1, 
          @output_file_name = N'', 
          @on_success_step_id = 0, 
          @on_success_action = 1, 
          @on_fail_step_id = 0, 
          @on_fail_action = 2
            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
          EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
    
          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
          -- Add the job schedules
          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
            @job_id = @JobID, 
            @name = N'HandmatigeUpload', 
            @enabled = 1, 
            @freq_type = 1, 
            @active_start_date = @intDate, 
            @active_start_time = @intTime
          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
          -- Add the Target Servers
          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver 
            @job_id = @JobID, 
            @server_name = N'(local)' 
          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
    
      END
      COMMIT TRANSACTION         
    
      GOTO   EndSave              
    
      QuitWithRollback:
          IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
      EndSave:
    GO

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    does a commit have to occur for the job addition prior to enabling the schedule?? ...might be worth a try..

  3. #3
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Quote Originally Posted by rocket39
    does a commit have to occur for the job addition prior to enabling the schedule?? ...might be worth a try..
    Thanks, but you don't believe it, this seems just a part of the solution, thanks so far! If I schedule the job for the next day, it works now fine. If I want to schedule it for today (e.g. next hour), it still won't enable the schedule.
    I tried now also to call sp_update_jobschedule, but that won't work.

    I created a template job from which I derived the script. This script I have used to construct this sp (doing this way I thought I could trust MS ). So, that's why the 'COMMIT TRANSACTION' statement was on the place where I leave it .

  4. #4
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52

    Thumbs up Problem solved

    Hi there,

    I found a solution, called work-around.
    I first create the jobschedule for the next day (date + 1), do a commit transaction and so on, and after that I update the jobschedule @active_start_date back to the current date. This works fine!

Posting Permissions

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