XP_SQLAGENT_ENUM_JOBS vs. SP_HELP_JOB

Question

XP_SQLAGENT_ENUM_JOBS vs. SP_HELP_JOB

imani_technology

SSCrazy Eights

Points: 8200
More actions
October 31, 2016 at 11:34 am

#150540

Let me start at the beginning. Here is what we are doing:
1. Control-M calls...
2. A Batch File that calls...
3. A Stored Procedure that runs...
4. A SQL Agent Job that processes...
5. An SSAS cube.
The status of the SQL Agent Job must ultimately be returned to Control-M.
Yes, I know that we can have Control-M process the SSAS cube directly, thereby bypassing all of those steps. However, that is not what the company wants, not my decision :-).
Anyway, I am using XP_SQLAGENT_ENUM_JOBS in my stored procedure to periodically retrieve the status of the job instead of SP_HELP_JOB. I know that XP_SQLAGENT_ENUM_JOBS is undocumented. However, I decided against using MSDB.dbo.sp_help_job because:
1. It generally requires OPENROWSET to put the resultset into a table, which requires additional overhead of an OLEDB connection.
2. It outputs more columns than xp_sqlagent_enum_jobs, which might also impact performance.
Is my reasoning sound? What are the counter arguments.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

I guess the question is: Is the overhead of using the OpenRowset() in the documented stored proc and the additional columns enough reason to use an undocumented function/stored proc? Do you have enough data to support your claims of "additional overhead"?

Agreed that none of us like to use OpenRowset() for various different reasons, but you also have to weigh the risk of using something that isn't documented. I'm not sure that I'd settle for the undocumented function/stored proc unless the overhead is substantial.

Also, have you considered other options like interrogating the SQL Agent tables in MSDB directly? Or using some kind of control table to indicate a successful completion? Not saying that these options are better, but they may carry less risk than what you are doing right now. You may also want to do a bit more digging into why the proc is undocumented. Could it be that it is deprecated?

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

I guess my main question is, what's wrong with using something not officially documented? What are the risks?

Also, doesn't OpenRowset require a connection string? How does that affect the portability of the stored procedure?

Finally, I'm not sure what you mean by interrogating the SQL Agent tables in MSDB directly? What tables do you mean?

Martin Schoombee (10/31/2016)
I guess the question is: Is the overhead of using the OpenRowset() in the documented stored proc and the additional columns enough reason to use an undocumented function/stored proc? Do you have enough data to support your claims of "additional overhead"?
Agreed that none of us like to use OpenRowset() for various different reasons, but you also have to weigh the risk of using something that isn't documented. I'm not sure that I'd settle for the undocumented function/stored proc unless the overhead is substantial.
Also, have you considered other options like interrogating the SQL Agent tables in MSDB directly? Or using some kind of control table to indicate a successful completion? Not saying that these options are better, but they may carry less risk than what you are doing right now. You may also want to do a bit more digging into why the proc is undocumented. Could it be that it is deprecated?

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 3

By SQL Agent tables, you mean dbo.sysjobactivity? Let me check that out, I didn't know about this!