Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Agent - Transact SQL script Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 1:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:34 AM
Points: 218, Visits: 712
Hi

If I execute 3 stored procedures in a SQL Agent Steps (type Transact-SQL script) like this:

exec storeproc1
exec storeproc2
exec storeproc3

If the execution of the second stored procedure failed, does the execution of storeproc3 will go thru or the job will failed after failure of storeproc2?

Thanks
Post #1468310
Posted Thursday, June 27, 2013 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 1,778, Visits: 5,729
the step will fail immediately when the second proc errors if they are all in one step, but if you have each proc in it's own step, you can configure it to continue on to step 3

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1468331
    Posted Thursday, June 27, 2013 2:04 PM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: 2 days ago @ 10:24 AM
    Points: 1,400, Visits: 2,636
    another option would be to encapsulate each proc in a try-catch block
    BEGIN TRY
    EXEC storeproc1
    END TRY
    BEGIN CATCH
    PRINT 'storeproc1 failed'
    END CATCH

    BEGIN TRY
    EXEC storeproc2
    END TRY
    BEGIN CATCH
    PRINT 'storeproc2 failed'
    END CATCH

    BEGIN TRY
    EXEC storeproc3
    END TRY
    BEGIN CATCH
    PRINT 'storeproc3 failed'
    END CATCH



    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help
    Post #1468339
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse