November 23, 2016 at 6:44 am
Hi all,
I have hundreds of SPs and I was thinking that maybe I could do a SP that runs all of the other ones, instead of creating a separate job step for all of them.
New SP's are still being developed and this would save me some time with not having to add new job steps withe the new SPs.
This is my TEST code that would run the SPs. It's easy grab the SP name from [SP_Names] put it in a parameter and execute it, grab the second one and so on.
DECLARE @sp-2 varchar(50)
DECLARE @cnt INT = 1
WHILE @cnt < (SELECT COUNT([SP_Names])+1 as SPsFROM [RISE_DATA].[dbo].[TEST])
BEGIN
SET @sp-2 = (SELECT '[RISE_DATA].[dbo].' + [Names] as SPsFROM [RISE_DATA].[dbo].[TEST] WHERE [SP_Names] like '%' + CAST(@cnt as varchar(10)) + '%' )
EXEC (@sp)
SET @cnt = @cnt + 1
END
I'm not sure if this is a good idea. I have my doubts about error handling. For example will the execution of the SPs stop if one of them fails?
Or if more than one of them fails will I be able to see the error for all of them?
I haven't done error handling before so I'm not sure about this whole thing.
Does anyone have any suggestions or ideas how to make this work?
November 23, 2016 at 6:47 am
davidvarga086 (11/23/2016)
Hi all,I have hundreds of SPs and I was thinking that maybe I could do a SP that runs all of the other ones, instead of creating a separate job step for all of them.
New SP's are still being developed and this would save me some time with not having to add new job steps withe the new SPs.
This is my TEST code that would run the SPs. It's easy grab the SP name from [SP_Names] put it in a parameter and execute it, grab the second one and so on.
DECLARE @sp-2 varchar(50)
DECLARE @cnt INT = 1
WHILE @cnt < (SELECT COUNT([SP_Names])+1 as SPsFROM [RISE_DATA].[dbo].[TEST])
BEGIN
SET @sp-2 = (SELECT '[RISE_DATA].[dbo].' + [Names] as SPsFROM [RISE_DATA].[dbo].[TEST] WHERE [SP_Names] like '%' + CAST(@cnt as varchar(10)) + '%' )
EXEC (@sp)
SET @cnt = @cnt + 1
END
I'm not sure if this is a good idea. I have my doubts about error handling. For example will the execution of the SPs stop if one of them fails?
Or if more than one of them fails will I be able to see the error for all of them?
I haven't done error handling before so I'm not sure about this whole thing.
Does anyone have any suggestions or ideas how to make this work?
If there is an error in one of the procs, what would you like to happen?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 23, 2016 at 6:57 am
What if someone creates a stored procedure that elevates their privileges? Are you protected from that?
November 23, 2016 at 7:05 am
Well I would like to know which one failed and why it failed. And maybe put that info in a table I guess.
And I might set up a mail notifications of the error's when I'm finished with all of this.
November 23, 2016 at 7:09 am
davidvarga086 (11/23/2016)
Well I would like to know which one failed and why it failed. And maybe put that info in a table I guess.And I might set up a mail notifications of the error's when I'm finished with all of this.
And do you want execution of the others to continue in the event of a failure?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 23, 2016 at 7:11 am
Luis Cazares (11/23/2016)
What if someone creates a stored procedure that elevates their privileges? Are you protected from that?
The thing is Only 5 people from my team have access to this Server and we don't really need to elevate our privileges 😉
November 23, 2016 at 7:15 am
Phil Parkin (11/23/2016)
davidvarga086 (11/23/2016)
Well I would like to know which one failed and why it failed. And maybe put that info in a table I guess.And I might set up a mail notifications of the error's when I'm finished with all of this.
And do you want execution of the others to continue in the event of a failure?
YES
They are all just small SPs that gather data and do an insert into a table.
Having a few missing would cause less issues than missing a lot of them.
November 23, 2016 at 7:23 am
davidvarga086 (11/23/2016)
Phil Parkin (11/23/2016)
davidvarga086 (11/23/2016)
Well I would like to know which one failed and why it failed. And maybe put that info in a table I guess.And I might set up a mail notifications of the error's when I'm finished with all of this.
And do you want execution of the others to continue in the event of a failure?
YES
They are all just small SPs that gather data and do an insert into a table.
Having a few missing would cause less issues than missing a lot of them.
OK. You need to put a TRY .. CATCH block around your EXEC to handle this, broadly along the following lines:
BEGIN TRY
Exec
END TRY
BEGIN CATCH
--Write to error log
END CATCH
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 23, 2016 at 7:45 am
Maybe you could use your script as a way to programmatically build the SQL Agent job using msdb.dbo.sp_add_jobstep
https://msdn.microsoft.com/en-us/library/ms187358.aspx
something like this but changing the parameters to better suit your needs...
USE msdb;
GO
DECLARE @jobid nvarchar(36) = (SELECT CAST(job_id AS nvarchar(36)) FROM dbo.sysjobs WHERE name = N'your job name');
SELECT CONCAT(N'msdb.dbo.sp_add_jobstep @job_id=',@jobid,N',@step_name=N',t.stepname,N',@step_id=',CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(3)),N',@cmdexec_success_code=0,@on_success_action=3,@on_success_step_id=0,@on_fail_action=3,@on_fail_step_id=0,@retry_attempts=0,@retry_interval=0,@os_run_priority=0, @subsystem=N''TSQL'',@command=N''[RISE_DATA].[dbo].''',[Names],N''',@database_name=N''yourDBname'',@flags=0') AS sqlcmd
FROM [RISE_DATA].[dbo].[TEST] t WHERE t.[SP_Names] LIKE '%' + CAST(@cnt as varchar(10)) + '%';
November 23, 2016 at 8:20 pm
Sounds like a SQL Service Broker scenario too, although I very much like the scripted SQL Agent job method too. I have used both for such scenarios.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 24, 2016 at 6:00 am
I'm on my phone so this is all based on memory.
What you have may work but if you want it to continue on failure you will have difficulties setting up alerts in the agent job because the one procedure will always succeed (after wrapping in try catch). You could add try catch to each individual stored procedure and separately log failures but alerts will be difficult.
I like the idea of scripting out the creation of the agent job. Maybe you could use a while loop where each job step is the SP name and then you could first check if job step exists based on name and create if not then update. The script will need to update each job step to renumber each job step in the case a stored procedure was added.
I can add code when back at computer.
November 25, 2016 at 8:51 am
Thanks all
I've decided at the end to go with the Try and catch option and it ended out quite alright 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply