August 30, 2012 at 5:48 am
--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 post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy