June 27, 2013 at 1:19 pm
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
June 27, 2013 at 1:50 pm
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 27, 2013 at 2:04 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply