Creat one sql job on 4 different environments same time in one script

  • --I need to know codes for GO TO NEXT STEP, and need to replace with script below

    --also I want to just fill it as per requirememt and increase steps if more than 1

    --can anyone customise better as far as schedules are concern that what I have done, thanks in advance

    USE [msdb]

    GO

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'JOB NAME FROM TXT FILE', --JOB NAME

    @enabled=1,--keep it 0, just deployed, as per requirement we can make it 1 so that alert mails can avoided

    @notify_level_eventlog=0,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'SOME DESCRIPTION IN TXT FILE', --DESCRIPTION

    @category_name=N'[Uncategorized (Local)]', --CATEGORY

    --Logins 4 environments

    --can I make like below so that on 4 environments at a time this script will execute??

    --Stage

    @owner_login_name=N'CORP\StaSQLExec',

    --DEV

    @owner_login_name=N'CORP\DEVSQLExec'

    --CT

    @owner_login_name=N'CORP\CTESQLExec'

    --RT

    @owner_login_name=N'CORP\EXTSQLExec' @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    --Steps

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,

    @step_name=N'step name from txt file', --step name -step1 from txt file

    --Step 1 --

    @step_id=1, --if more than step 1 then repeate block

    @cmdexec_success_code=0,

    @on_success_action=1, -- make it 2

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'SSIS',

    @command=N'/SQL "\DTS NAME FOR STEP 1" /SERVER npkstadat11 /CONFIGFILE "E:\SSIS_Package_Repository\DTS NAME FOR STEP 1\PackageDependencies_20120801\DTS.dtsconfig" /CHECKPOINTING OFF /REPORTING E',

    @database_name=N'master',

    @flags=0

    --Step 2 --

    @step_id=2, --if step 3 then repeate block

    @cmdexec_success_code=0,

    @on_success_action=1, -- what is GO TO NEXT STEP code here??

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'SSIS',

    @command=N'/SQL "\DTS NAME FOR STEP 2" /SERVER npkstadat11 /CONFIGFILE "E:\SSIS_Package_Repository\DTS NAME FOR STEP 1\PackageDependencies_20120801\DTS.dtsconfig" /CHECKPOINTING OFF /REPORTING E',

    @database_name=N'master',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'JOB NAME',

    @enabled=0, --keep 1 so that alert mails can be avoided

    @freq_type=8,

    @freq_interval=127,

    @freq_subday_type=1,

    @freq_subday_interval=0,

    @freq_relative_interval=1,

    @freq_recurrence_factor=1,

    @active_start_date=20080318, --start date

    @active_end_date=99991231, -- end date

    @active_start_time=91500,

    @active_end_time=235959,

    @schedule_uid=N'a4c6128a-c053-4041-bb07-53012cee1527'

    -- what can we put here?

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

Viewing 0 posts

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