SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
himanshuk
himanshuk
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
--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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search