0

I have a stored procedure that is run based on plant name.

I have a maintenance plan that calls the stored procedures for all plants in a timezone like

execute dbo.runPlantSP('plant1')
execute dbo.runPlantSP('plant3')
execute dbo.runPlantSP('plant55')

The issue I am facing is if there is an error that occured while execute dbo.runPlantSP('plant1') runs then it never runs for 'plant3' or 'plant55'. Is there a setting in the maintenance plan I can change to make it still attempt the next line? Or do I need to change the internals of my stored procedure to handle errors so that if something happens in the stored procedure for 'plant1', then we have a catch that handles it and it does not bubble up to the maintenance plan stopping it?

Brian Karabinchak
  • 207
  • 1
  • 9
  • You have a maintenance plan that just runs a bunch of arbitrary procedures? Why isn't this just a normal SQL Server Agent job? Anyway you can wrap each call with `TRY/CATCH`... – Aaron Bertrand Feb 13 '23 at 16:42
  • I may be using the wrong terminology I only ever have to touch this stuff when something goes wrong. I have a maintenance plan, and within that subplans per time zone. Within each timezone is a little block that says `Execute T-SQL Statement Task`, which is where all the calls are. I think the original author did it this way because there are something like 100 plants and so he wrote some code to write the code to handle each plant separated by time zone so he could copy/paste into these T-SQL Statement task boxes. But thank you I will look into both of those. – Brian Karabinchak Feb 13 '23 at 16:51
  • Is there config or a table somewhere that tells you which time zone a plant is in? If not how do you know the maintenance plan is even right? Anyway if this is derivable somewhere there is certainly a much more efficient and scalable approach than copying and pasting a bunch of commands into different task boxes. – Aaron Bertrand Feb 13 '23 at 16:53
  • Yea we have multiple configuration tables saying what tables from what schemas for each plant need to be backed up. QA has been checking between the two data sources. I think there's some quality control queries they have but that is not something I have any power over. I just now there is some QA person looking at both. I don't think I said it in the OP but these stored procedures are for backing up tables to SQL Servers that are Linked Servers. – Brian Karabinchak Feb 13 '23 at 16:54

0 Answers0