how to get this into a table?

Hi all,

Can any body please suggest me how to get the result from the following procedure into a table.

sp_help_job @.Execution_status=1

I just cannot get the result from this procedure into a table

Thanking in advance

Jacx

To get the o/p of a sp to a table the table should already be existing. The output columns should match the table structure. if the table exists

then

Insert into Tablename Exec yoursp

Madhu

|||

i will give u a simple solution. now ifu see the o./p of sp_help_job @.Execution_status=1 then u can see that there are many column. So the simple method is , first findout the script for sp_Hlep_job. for that run sp_helptext sp_help_job.When u run this query, u will get the source code of sp_help_job, and u can see the data type or you can make a wrapper which will automatically create a table for u

Madhu

|||

this is the script for the table i created for geting the results

CREATE TABLE [Tbl_JobDET] (
[Job_id] [uniqueidentifier] NULL ,
[Originating_server] [varchar] (255) NULL ,
[Name] [varchar] (255) NULL ,
[enabled] [int] NULL ,
[description] [varchar] (255) NULL ,
[start_step_id] [int] NULL ,
[Category] [varchar] (255) NULL ,
[owner] [varchar] (255) NULL ,
[notify_level_email] [int] NULL ,
[notify_level_netsend] [int] NULL ,
[notify_level_page] [int] NULL ,
[notify_email_operator] [int] NULL ,
[notify_netsend_operator] [int] NULL ,
[delete_level] [int] NULL ,
[date_created] [datetime] NULL ,
[date_modified] [datetime] NULL ,
[Version_number] [int] NULL ,
[last_run_date] [int] NULL ,
[last_run_time] [int] NULL ,
[last_run_outcome] [int] NULL ,
[next_run_date] [int] NULL ,
[next_run_time] [int] NULL ,
[next_run_schedule_id] [int] NULL ,
[current_execution_status] [int] NULL ,
[current_execution_step] [int] NULL ,
[current_retry_attempt] [int] NULL ,
[has_step] [int] NULL ,
[hsa_schedule] [int] NULL ,
[has_target] [int] NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

and i tried geting the results using this

insert tbl_jobDET
EXEC sp_help_job @.execution_status=1

and this is the Error i got

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Any suggestions on how can i resolve this error and get the result into the table ?

|||

Jacx,

A possible solution could be creating a linked server pointing to itself (loopback) and using:

select *

into #t

from openquery(Loopback, 'set fmtonly off; exec msdb.dbo.sp_help_job @.execution_status = 1')

go

select *

from #t

go

drop table #t

go

AMB

|||Jacx,

I was looking for the same, and came across this page:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=259078

It contains this suggestion, which works great:

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @.execution_status=4')

You may have to change your connection string or sp_help_job parameters...