xp_sqlagent_enum_jobs documentation?

_{Wednesday, June 25, 2008 2:55 PM | 1 vote}

I found a stored procedure called xp_sqlagent_enum_jobs that contains job data that I want to use.  But I can not find any documentation on the params, options, data types returned, etc.  I was able to derive some of it based on the code in sp_get_composite_job_info.  Can anyone tell me where I can find documentation on xp_sqlagent_enum_jobs?

I already searched BOL (no reference at all) and the web, so if you have specific links please provide.

Thanks.

_{Wednesday, June 25, 2008 3:05 PM ✅Answered}

This is an undocumented procedure in SQL Server, and to get information on how it is used/what parameters usually requires doing a google/msn/yahoo/whatever search engine search and digging through what is out there for information.  Some of these are covered better than others on the web.  One thing to keep in mind.  These are undocumented and this means that there is no guarantee for how they will function or if they will exist in future service packs or releases.  Microsoft makes no guarantees about these procedures, so using them in production code is done at your own risk.

_{Wednesday, June 25, 2008 3:55 PM ✅Answered | 3 votes}

The Documented SP for this is sp_help_job.  It eventually calls the xp_sqlagent_enum_jobs procedure, If you created a Table to hold the results from this procedure, you could find plenty of documentation in it.

http://msdn.microsoft.com/en-us/library/ms186722.aspx

_{Wednesday, June 25, 2008 4:36 PM ✅Answered | 2 votes}

If you view the code for msdb.dbo.sp_help_job you'll see a call to msdb.dbo.sp_get_composite_job_info, which in turn calls xp_sqlagent_enum_jobs. In fact if you set SQL Profiler running and then open the Job Activity Monitor you'll see that sp_help_job is called - so this is how Microsoft obtains the job status information and I doubt there'd be any other way using just T-SQL. An alternative could be to write your own CLR stored proc that uses SMO to query job status then returns the data as a resultset.

As others have said there's a risk with using undocumented stored procs. If you're coding an app just for personal/administrative use then the risk is minimal,as at worst you can simply update your code if Microsoft do decide to change the way in which job status info is obtained.

If you decide to go down the CLR route you'd end up with a robust solution, but the time spent coding the solution might be greater than the time taken to update your T-SQL stored proc in future should Microsoft decide to change the behaviour of xp_sqlagent_enum_jobs.

The choice is yours but I'd advise that if you do use xp_sqlagent_enum_jobs that you document what you've done and why and what steps should be taken to rectify any future errors - the best place for this might be as a comment block within your stored proc. You might want to consider marking the code with a placeholder, such as ** UNDOCUMENTED ** so that you can easily find such usage by querying sys.sql_modules or the scripts that you keep under source control.

Chris

_{Wednesday, June 25, 2008 6:34 PM ✅Answered | 1 vote}

This should work.

Code Snippet

SELECT * INTO #JobInfo

FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'

, 'set fmtonly off exec msdb.dbo.sp_help_job')

Select * from #JobInfo

_{Wednesday, June 25, 2008 3:38 PM}

Thanks for the response.

I am aware that undocumented code is not supported, but its a very valid point and in the back of my mind.  I am just setting up a job that returns and XML doc of job status values via SSIS.  So its not a critical in the sense of production quality code, but I do want it to be stable and return valid results.

Does Microsoft use these undocumented sps?  Why don't they provide documentation, or if they are not used why not clean up and remove them?

Another question I would ask; do you know of a better way to get the information presented in that stored procedure via an accepted format?  I could query all the tables and build my own result set, but not optimal.  I panned to use some other sp_ commands but I ran into INSERT EXEC can not be nested issues, so the result set for all jobs provided to be an issue.

Here is the code I am using so far (planning to tweak the date logic yet)

Code Snippet

/*

RETURNS INFORMATION LIKE sp_get_composite_job_info

CREATED TO AVOID SERVER CONIFGURATION CHANGES WHEN ABOVE WAS USED

*/

/* CREATE A TEMP TABLE TO HOLD RESULTS OF STORED PROECUDRE CALL */

DECLARE @JobInfo TABLE (

Job_Id UNIQUEIDENTIFIER NOT NULL,

Last_Run_Date INT NOT NULL,

Last_Run_Time INT NOT NULL,

Next_Run_Date INT NOT NULL,

Next_Run_Time INT NOT NULL,

Next_Run_Schedule_Id INT NOT NULL,

Requested_To_Run INT NOT NULL,

Request_Source INT NOT NULL,

Request_Source_Id SYSNAME COLLATE database_default NULL,

Running INT NOT NULL,

Current_Step INT NOT NULL,

Current_Retry_Attempt INT NOT NULL,

Job_State INT NOT NULL)

/* VARIABLE NECCESSARY TO RUN STORED PROCEDURE */

DECLARE @SysAdmin INT

DECLARE @SName SYSNAME

SET @SName = SUSER_SNAME()

/* VERIFY ACCOUONT HAS ACCESS TO THIS INFORMATION */

SELECT @SysAdmin = ISNULL(IS_SRVROLEMEMBER(N'SysAdmin'), 0)

IF (@SysAdmin = 0)

BEGIN

SELECT @SysAdmin = ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0)

END

/* GET BASE SQL JOB DATA */

INSERT INTO @JobInfo

EXECUTE master.dbo.xp_sqlagent_enum_jobs @SysAdmin, @SName, NULL

/* RETURN DATA */

SELECT

J.[Name] AS [Name],

J.Enabled AS [Enabled],

SJS.Last_Run_Outcome AS [Last_Run_Outcome],

JI.Job_State AS [Current_Execution_Status],

CASE

WHEN ISNULL(JI.Last_Run_Time, 0) = 0 OR ISNULL(JI.Last_Run_Date, 0) = 0 THEN

'Not Available'

ELSE

CONVERT(VARCHAR,

CONVERT(DATETIME,

SUBSTRING(CAST(JI.Last_Run_Date AS VARCHAR(8)), 5, 2) + '/' +

RIGHT(CAST(JI.Last_Run_Date AS VARCHAR(8)), 2) + '/' +

LEFT(CAST(JI.Last_Run_Date AS VARCHAR(8)), 4) + SPACE(1) +

STUFF(STUFF(

REPLICATE('0', 6 - LEN(JI.Last_Run_Time)) + CAST(JI.Last_Run_Time AS VARCHAR(6))

, 3, 0, ':')

, 6, 0, ':')

)

, 100)

END [LastRunDateTime]

FROM msdb.dbo.SysJobs J

INNER JOIN @JobInfo JI ON

(J.Job_Id = JI.Job_Id)

INNER JOIN msdb.dbo.sysjobservers SJS ON

(J.Job_Id = SJS.Job_Id)

ORDER BY J.[Name]

--FOR XML PATH ('Job'), ROOT('JobList')

_{Wednesday, June 25, 2008 4:12 PM}

That is where I went first.  But you can't insert the result set into anything.  So how do i get it to XML format?

If you try to insert it into a table you get the following error (below), and that is how I eneded up where I am now, trying to get the same data.

An INSERT EXEC statement cannot be nested.

_{Wednesday, June 25, 2008 4:43 PM}

There is a workaround listed here.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1707794&SiteID=17

_{Wednesday, June 25, 2008 5:12 PM}

I wrote a wrapper CLR TVF for sp_help_job that allows you to issue a select and get a table return that you can add filtering with where clauses to a bit back to simplify some of my own internal stuff.  The code is freely available on the following link:

Using a CLR TVF to SELECT Job Execution Status

SMO is not available in the hosted CLR for SQL Server, so you can't go that route.  You can't register the assembly which is very unfortunate since it would provide alot of extra power to SQL CLR.

_{Wednesday, June 25, 2008 5:16 PM}

Oops, my mistake. 

That's a shame as it would be useful for scenarios like this.

I still stand by my comments that the OP could consider using the undocumented xp if they are aware of the risks - who's to say that the output from sp_help_job won't change with the next release/patch of SQL Server (but at least the changes would be documented).

Chris

_{Wednesday, June 25, 2008 6:16 PM}

Interesting solution.  I guess I would ask, is it good practice to reconfigure the server to run code, and then reconfigure it back?  What are the impications of doing that (impact on conncetions, stability of system, ovehead, etc)?  It seems like a solution that is more of a hack then elegant.

_{Thursday, June 26, 2008 11:33 PM}

This I think was one of the most baffling things to me when I first started writing CLR objects in SQL Server.  To me it seemed like it should be a no brainer that SQL Managment Objects should be able to be used inside of SQL Server, but a combination of things about SMO make it unusable, even in UNSAFE assemblies.  Bob Beauchemin details this a little on his blog:

http://www.sqlskills.com/blogs/bobb/2007/03/13/TwoThingsYouCantDoInSQLCLR.aspx

Service Broker could be used but that gets a bit complex.

_{Monday, January 4, 2010 1:32 PM}

Bravo :)

_{Tuesday, June 18, 2013 2:55 PM}

No, there is not.