April 2, 2008 at 5:56 am
I am trying to gather job information from sp_help_job, but can't figure out how to get the information from this sp into either another sp/view or table.
Tim
April 2, 2008 at 6:08 am
create a #tmptb according to the output you expect (check BOL !!)
then just perform
insert into #tmptb
exec sp_help_job
"et voila" you have the results in the temp table.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2008 at 7:18 am
When I do that I receive the error::crying:
"Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested."
There are 'INSERT' statements in various other procedures called by sp_help_job.
Is there anything else I can try?
Tim
April 2, 2008 at 7:51 am
keep in mind the output may varry according to your input parameters (check bol)
Can you post your exact statement ?
Indeed it gives an error, but the temptb is being filled up ...
create table #tmptb (
job_id uniqueidentifier ,
originating_server nvarchar(30) ,
name sysname ,
enabled tinyint ,
description nvarchar(512) ,
start_step_id int ,
category sysname ,
owner sysname ,
notify_level_eventlog int ,
notify_level_email int ,
notify_level_netsend int ,
notify_level_page int ,
notify_email_operator sysname ,
notify_netsend_operator sysname ,
notify_page_operator sysname ,
delete_level int ,
date_created datetime ,
date_modified datetime ,
version_number int ,
last_run_date int ,
last_run_time int ,
last_run_outcome int ,
next_run_date int ,
next_run_time int ,
next_run_schedule_id int ,
current_execution_status int ,
current_execution_step sysname ,
current_retry_attempt int ,
has_step int ,
has_schedule int ,
has_target int ,
type int )
insert into #tmptb
exec sp_help_job
select *
from #tmptb
-- cleanup
drop table #tmptb
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2008 at 8:23 am
Tim Riley (4/2/2008)
When I do that I receive the error::crying:"Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested."
There are 'INSERT' statements in various other procedures called by sp_help_job.
Is there anything else I can try?
Tim
Use OPENROWSET?
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply