Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Unanswered: freaking out job!!!

    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...

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    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

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    i have done what you said and all the retrys are at 0........?

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    still doesnt work

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you script the job abd post/attach it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    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?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, I love those

  11. #11
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    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_Vérification', @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_Détail', @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
    -- Vérifier 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:

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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

  13. #13
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    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
    -- Vérifier 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

  14. #14
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    shes a tuffy hehe

  15. #15
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    can someone please respond to this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •