February 14, 2008 at 5:53 pm
We have the problem where our nightly maintenance jobs are running into the next business day and I am wanting to write a procedure to identify all running jobs into a temp table and loop through the temp table and decide whether to kill them based on the job name. I tried creating a temp table called #RunningJobs and executing
DECLARE @sql varchar(100)
SET @sql = 'exec msdb.dbo.sp_help_job @execution_status = 1'
insert into #RunningJobs EXEC(@sql)
I get the error
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
Does anyone know the way to get the results of this proc into a temp table or an alternative method to accomplish my goal here
Thanks
February 14, 2008 at 6:57 pm
You could try creating your own version of sp_help_job. Just script the existing one to a new window as CREATE, change the name and fiddle the output to go to a named table rather than a temporary one.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 14, 2008 at 7:54 pm
create table #RunningJobs
(
... column definitions ...
)
insert into #RunningJobs
exec msdb.dbo.sp_help_job @execution_status = 1
February 14, 2008 at 8:02 pm
That gives this error which is what I originally tried
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply