i have created a sql server job that i have scheduled to run every 10 min. in this job i have diferent steps wich seem to be working fine i also have 2 alerts that i have created for this job in case some steps fails so that i can be notififed via email with specific messages. the Weird thing is this job keeps re executing over and over again and never whaits for that 10 min delay and of course i get flooded with emails since i have set up test data to get these messages on purpose. so y doesnt wait 10 min like i have scheduled .PLZ HELP...Somewhere you are going into a loop ...
Check the Advanced properties for the job steps .. ?
Check on success , on faliure and retry attempts ... the error seems to be from somewhere there only.|||i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP|||Originally posted by hillcat
i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP
I always set it up as go to next step on success and quit on failure.Did you check the no of retry attempts also|||i have done what you said and all the retrys are at 0......?|||still doesnt work|||Hmmm .. that is wierd !!!
Will have to do some research here ... try to find the step after which the job re-executes
Can do so by ... changing the advanced option .. on success of each step one by one from the bottom upwards to quit the job returning success.|||Why don't you script the job abd post/attach it?|||wait a minute. Did you say you put every step to go to step A on success? So step 1 (on success) -> step 2 (on success) -> step 1?
Isn't this a perfect endless loop?|||Yup, I love those ;)|||BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Commander') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Commander'
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Test_Upload')
IF (@.JobID IS NOT NULL)
BEGIN
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Test_Upload'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Test_Upload'
SELECT @.JobID = NULL
END
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'Test_Upload', @.owner_login_name = N'sa', @.description = N'Execute package: Test_Upload Commandes', @.category_name = N'Commander', @.enabled = 1, @.notify_level_email = 2, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0, @.notify_email_operator_name = N'Email'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Test_UpLoad_En cours', @.command = N'DTSRun /~Z0x3C2C60E6E69FDE4FD4E6E831CD32E2FC93EBDFD722093E B44F362DAB46FD1068596FBE179AAB0CBFC9253FBE160DD1AC 517927A330B272A8182D277B7FE8A0B25807CCA4CC03F11C88 59B5558AB6C1C60AB73140DD13B81B818692A28E23714096F3 C81B31D380168FE17CAB4BE11405820A0A411D90300F55E071 9CFF7A9D4B7BEA8A', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Test_Upload_Vrification', @.command = N'DTSRun /~Z0x77673A40FE51DCB3FDE10E99A1046569DE918CA37A09EA 1936C2699A12469122352F4D900875502D1ABCDEEB38E19B7A A4EEAEB430318EF008E74C940CEEDA61F3128D9C3658E57A07 692F84203B7CDE3FD074CAF73B5B7475F2A2DF33D3CDB9BA2B B1C7909B2656915BD9D8B5695DAD0A14723D9C40A502744A73 559B604CDCA4EAB538C7243875A187BA', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 3, @.step_name = N'Test_Upload_Validation', @.command = N'IF (SELECT PC_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
PRINT ''Upload en cours dans Pc''
END
ELSE
IF (SELECT MAESTRO_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Upload en cours dans Maestro'', 16, 1)
END
ELSE
IF (SELECT COPY_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Copy Upload dans Maestro '', 16, 1)
END
', @.database_name = N'AS400', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 4, @.step_name = N'Test_Upload Commandes', @.command = N'DTSRun /~Z0x5F4F7BA66CB617B86BCC563E86B5C07376BFA0C96766A8 972F7E2AE768C0D2D4B5921094A7CFDAF205EAD298C872263D B963EDDEE1E6D5029CCB81BD1ECFF3932DF1A04B107B2B1514 C18176F412201E686A7DC37753F0D7036BC4076C1B87A404EE 673B7DBDC60F860ABED0E9F04600EA05DC37239265F49D18C9 26FC683E0E542F40E7B838BB2664CF12', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 5, @.step_name = N'Test_Upload Collections', @.command = N'DTSRun /~Z0xC4DCE73BFF8EB8958A569CF001E7764EDCA3DBA6CD29A1 3E9150B7C34F88FD0EA3114E6F6CA688F0E6D03DCC2B0B0996 1E529D40BC65AED39E97EBF3153D41F9A663C8AAEBB9D62DDE 119F217125C85BA50A662F8EEA95E08B5ECAB21C3627000E13 CAC07CA608E402106F5ECA139DA115EE94F9C5709FDCF46256 2B00F3020D772FBBCBD9D77397E3BD3A398CB8D8E360998F ', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCollectionsStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 6, @.step_name = N'UpLoad_V_Entete', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)
DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM EntCom
WHERE UpdCode = ''C''
OPEN TransactionNb_cursor
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EntCom
IF (SELECT UpdCode
FROM DetCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50005, 10, 0, @.TransactionNb, @.EqId)
END
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END
CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 7, @.step_name = N'UpLoad_V_Dtail', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)
DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = ''C''
OPEN TransactionNb_cursor
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s''il existe une transaction avec le UpdCode = ''C'' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END
CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 8, @.step_name = N'Test_UpLoad_Demande', @.command = N'DTSRun /~Z0xBCAC64110FBAE51F592FE69C2AD17FA3221C1CD7655D78 57A08FFE18C4FCA0A3F030E6ED91BD8962BBDE9C3FCDC5F340 ED2D68443C7577C5DBD2AD344EA9A1CED6568FA45E339E95C9 831E4DD5D783C0C52D2BD5DD5964040600AA94A3272BB3AB2A 5A5C9F9AAA3F428F8B4D327E4307B6E379BAAEAE4DCBC5F1EB 02416E763CEBCE05', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.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'Test_Upload Commandes', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20031020, @.active_start_time = 60000, @.freq_interval = 62, @.freq_subday_type = 4, @.freq_subday_interval = 10, @.freq_relative_interval = 1, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 220000
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:|||Originally posted by Brett Kaiser
Why don't you script the job abd post/attach it?
Well, you asked for it, and hillcat ran with it ;)|||but it hink that its this part that doesnt not work
DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)
DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'
OPEN TransactionNb_cursor
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END
FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END
CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor|||shes a tuffy hehe|||can someone please respond to this|||This is the same as the other thread, isn't it?
Anyway, make sure the on success and on failure directives are not looping.
Make sure you use WHILE @.@.FETCH_STATUS = 0
Using <> -1 could cause it to loop unexpectedly.
