May 8, 2009 at 10:15 am
We are using a windows service to start a stored procedure in SQL 2005 sp3. Based on the division, the stored procedure can start 1 or more SQL Agent jobs that are configured to start SSIS packages with the appropriate divisions parameters. Since the jobs start right away and return immediately to the stored procedure, we want to monitor the status until the jobs complete and return a result set back to the service. The service runs under a limited account, so I don't want to give too many rights to the account. I found that if I run xp_sqlagent_enum_jobs, then I have to grant execute on this to the user. Since this is in the master db, I don't want to do that. I tried running sp_help_job which returns the correct data and only needs rights to the already existing SQLAgentOperatorRole in msdb, which is more preferable. The question I have is how to read the results of this proc in the calling proc. When I try to insert it into a table variable 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."
Any suggestions?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2009 at 9:01 pm
You are getting this error because SP_HELP_JOB calls "sp_get_composite_job_info" which in turn tries to use INSERT..EXEC to capture the output from "xp_sqlagent_enum_jobs".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 11, 2009 at 6:17 am
RBarryYoung (5/10/2009)
You are getting this error because SP_HELP_JOB calls "sp_get_composite_job_info" which in turn tries to use INSERT..EXEC to capture the output from "xp_sqlagent_enum_jobs".
I understand why I'm getting the error. I'm wondering if anyone can suggest a workaround to it
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2009 at 6:54 am
Mike Martin (5/11/2009)
RBarryYoung (5/10/2009)
You are getting this error because SP_HELP_JOB calls "sp_get_composite_job_info" which in turn tries to use INSERT..EXEC to capture the output from "xp_sqlagent_enum_jobs".I understand why I'm getting the error. I'm wondering if anyone can suggest a workaround to it
I've been thinking about it, but it's hard to tell what might fit your circumstances...
First thing is, there's no fix for the nested INSERT..EXEC problem other than re-writing the inner procedure(s) to return their data a different way to the outer procedure (temp tables usually). If you did that, it would just lead you back to your original problem though, because the inner routines are also calling xp_sqlagent_enum_jobs.
Therefore, I think that you should just be using xp_sqlagent_enum_jobs directly, yourself, which means that you will have to address the security issue somehow. I think your choices are:
1) As you initially said, just give the user's execute access to xp_sqlagent_enum_jobs.
2) Write a wrapper procedure, local to your DB with a certificate that will allow it to execute master.dbo.xp_sqlagent_enum_jobs. Unfortuantely, I am a little weak on what all this requires.
3) Same as (2), but instead of a certificate, give it an owner that can execute master.dbo.xp_sqlagent_enum_jobs. This may be a problem because you will need to make your local DB Turstworthy, and may have to enable CrossDB-Chaining (not sure for master).
4) Write the wrapper procedure as in (2), but put it in an already trusted DB, like master or msdb with dbo as the owner. Then give your users execute-access only to it. This should work but has the problem that you (or the servers DBA's) may not like putting the proc in master or msdb, plus it makes any temp table handling a bit trickier (pretty sure it's doable though).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 10:53 am
Hi Mike,
I can think of two possible approaches:
[1] Roll something similar to sp_help_job ...
use [msdb]
select
jb.[name],
ja.session_id,
STATUS_CD =
CASE
WHEN ja.start_execution_date IS NOT NULL AND
ja.stop_execution_date IS NULL THEN 'Running'
ELSE
'Idle'
END,
ja.run_requested_date,
ja.start_execution_date,
ja.stop_execution_date,
ja.job_history_id,
ja.last_executed_step_id
from msdb.dbo.sysjobs jb
left join
(
select
job_id,
max(run_requested_date) as max_run_date
from msdb.dbo.sysjobactivity
group by
job_id
) x
on x.job_id = jb.job_id
left join msdb.dbo.sysjobactivity ja
on ja.job_id = x.job_id
and ja.run_requested_date = x.max_run_date
order by
ja.run_requested_date desc
[2] Or, you need to execute master.dbo.xp_sqlagent_enum_jobs in some way.
If you are concerned about adhoc executes for xp_sqlagent_enum_jobs from the limited account, you could wrap this proc in another stored proc, that runs as database owner, so that you only need to grant the Dbo execute privileges on xp_sqlagent_enum_jobs. You will need a SysDBA to install this proc.
CREATE PROCEDURE dbo.usp_GET_JOB_STATUS
(
@job_guid UNIQUEIDENTIFIER
)
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @job_status TABLE
(
JOB_ID UNIQUEIDENTIFIER,
LAST_RUN_DATE VARCHAR(20),
LAST_RUN_TIME VARCHAR(20),
NEXT_RUN_DATE VARCHAR(20),
NEXT_RUN_TIME VARCHAR(20),
NEXT_RUN_SCHEDULE_ID INT,
REQUESTED_TO_RUN INT,
REQUEST_SOURCE INT,
REQUEST_SOURCE_ID VARCHAR(100),
RUNNING INT,
CURRENT_STEP INT,
CURRENT_RETRY_ATTEMPT INT,
[STATE] INT,
PRIMARY KEY ( job_id )
);
INSERT INTO @job_status
EXEC master.dbo.xp_sqlagent_enum_jobs 1, 'N/A' ;
SELECT
STATUS_CD =
CASE
WHEN t.[STATE] = 1 THEN 'Running'
WHEN t.[STATE] = 2 THEN 'Waiting'
WHEN t.[STATE] = 3 THEN 'Retrying'
WHEN t.[STATE] = 4 THEN 'Not Running'
WHEN t.[STATE] = 5 THEN 'Suspended'
WHEN t.[STATE] = 7 THEN 'Completing'
ELSE '(Unknown)'
END ,
sj.JOB_ID,
sj.[NAME],
t.LAST_RUN_DATE ,
t.LAST_RUN_TIME ,
t.RUNNING,
t.[STATE]
FROM [msdb].[dbo].[sysjobs] sj WITH (NOLOCK)
LEFT JOIN @job_status t
ON sj.JOB_ID = t.JOB_ID
WHERE sj.JOB_ID = @job_guid;
END;
GrayB
May 14, 2009 at 11:56 am
Graham,
thanks for the info. This is what I was looking for. It looks like I can use your 1st option
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply