Answered by:

xp_sqlagent_enum_jobs documentation?

Question

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.

Answers

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.

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.

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.

All replies

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.

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)

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.

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.

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.

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:

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.

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).

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.

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: