Query regading sp_help_job

Question

Post reply

Query regading sp_help_job

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
May 17, 2009 at 11:42 pm

#384606

[font="Verdana"]Hi,
I have one job scheduled. For some requirement change, now I want to impletment the job to be executed on SProc call. That reason I need to store the resultset returned by sp_help_job 'id', 'job_name' in temporary table. Does somebody have any idea how to do this? I tried the below thing however it won't allow me to create the table at run time like Select * Into (run time table)
Insert Into (db)..JobDetails
Exec sp_help_job Null, 'job_name'
Do I need to create the temp tables first and then insert the resultset into it so that I can refer it further?
Any help appreciated,
thanks in advance.
Mahesh
[/font]
MH-09-AM-8694

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello Mahesh,

Are you executing the SP from the msdb DB?

e.g Exec msdb.dbo.sp_help_job Null, 'job_name'

If so, what error do you get?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Thanks John,

Further I tried to create hash table. But actually sp_help_job SPorc returns 4 resultsets. I tried like

Insert Into #JobDetails

Exec sp_help_job Null, 'job_name'

However it is throwing following error:

Core Job Details:

=================

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested.

Job Steps:

==========

Job Schedules:

==============

Job Target Servers:

===================

(0 row(s) affected)

Can you please suggest on this?

[/font]

MH-09-AM-8694

John H Marsh SSChampion Points: 11671 More actions · Answer 3

Hello Mahesh,

You will need to specify a value for the “Job_Aspect” parameter

e.g Exec msdb.dbo.sp_help_job Null, 'job_name', ‘JOB‘

In which case the SP will return only one result set.

Having just tested it myself I realised that sp_help_job must internally be doing a similar insert, hence the “Nested Insert Exec” error.

I believe to get around this you would need to use the OpenRowset technique descibed in this thread:-

http://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

[font="Verdana"]Thanks again John. However its still throwing the below error

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested.

Mahesh

[/font]

MH-09-AM-8694

John H Marsh SSChampion Points: 11671 More actions · Answer 5

Hello Mahesh,

Please see my additional comment above about the OpenRowSet workaround.

Another option would be to gather the data directly from the msdb.dbo.sysjobXXXXX Tables/Views.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

krayknot SSCertifiable Points: 7998 More actions · Answer 6

Mahesh Bote (5/17/2009)
[font="Verdana"]Hi,
I have one job scheduled. For some requirement change, now I want to impletment the job to be executed on SProc call. That reason I need to store the resultset returned by sp_help_job 'id', 'job_name' in temporary table. Does somebody have any idea how to do this? I tried the below thing however it won't allow me to create the table at run time like Select * Into (run time table)
Insert Into (db)..JobDetails
Exec sp_help_job Null, 'job_name'
Do I need to create the temp tables first and then insert the resultset into it so that I can refer it further?
Any help appreciated,
thanks in advance.
Mahesh
[/font]

You need to use temporary table for ex:

This sample sp generates a dataset

alter procedure tempsp

AS

select name from sys.tables

Return

and by using the below statement you can take the output in a table.

create table #temp(mTemp varchar(100))

Or, if you find the other alternative pls let me know

Insert into #temp exec tempsp

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

[font="Verdana"]

John Marsh (5/18/2009)
Hello Mahesh,
Please see my additional comment above about the OpenRowSet workaround.
Another option would be to gather the data directly from the msdb.dbo.sysjobXXXXX Tables/Views.
Regards,
John Marsh

Thanks John, OPENROWSET method works. Thanks everybody for your inputs.

Mahesh[/font]

MH-09-AM-8694

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

[font="Verdana"]Hi John,

Here is my last question. What seetings do I need to turn on / off while calling a job inside SProc? what thing I need to take care? Does it is a good practice?

Thanks,

Mahesh[/font]

MH-09-AM-8694

John H Marsh SSChampion Points: 11671 More actions · Answer 9

Hello Mahesh,

I guess you are planning to use msdb.dbo.sp_start_job to immediately execute a Job from an SP – correct?

In which case, I presume you have read through the Permissions section in the relevant BOL Topic: “sp_start_job (Transact-SQL)”.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group