InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 01:28:34 GMT20http://www.sqlservercentral.com/Forums/Topic1352123-110-1.aspx--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[quote]USE [msdb]GOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO[/quote]Thu, 30 Aug 2012 05:48:24 GMThimanshuk