Start & monitor a job to completion using T-SQL?

Question

Post reply

Start & monitor a job to completion using T-SQL?

Viewing 7 posts - 16 through 21 (of 21 total)

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

DarylAndDaryl Default port Points: 1491 More actions · Answer 1

Both methods (listed below) work on SQL Server 2000 and 7.0. Can anyone explain why one might be better than the other?

Method1:

EXEC master.dbo.xp_sqlagent_enum_jobs 1, dbo

Method2:

select *

from

openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q

--where q.name = MYJOBNAME

noeld SSC Guru Points: 96590 More actions · Answer 2

In this case I don't think there are many clear advantages of one over the other method. But Here are some points for you to consider.

1. Method1 is using an undocummented feature directly

2. Method2 opens a second conection to get the results

3. Method2 can be used on a join directly while method1 needs a tmp table

4.Method2 is invoking a procedure on msdb which on some enviroments may not be possible and method1 uses master which by design (MS) the user has access to.

There may be others but this is just to shed some light on the alternatives

HTH

* Noel

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 3

Darryl: I went with the script I wrote because I wasn't getting useful results from sp_help_job. So calling it from openrowset still won't give me joy.

There is no "i" in team, but idiot has two.

noeld SSC Guru Points: 96590 More actions · Answer 4

Dave,

sp_help_job WORKS for me! (with and without openrowset)

I know there are a lot of factors that can influence the final outcome but without further information those are usually hard to find.

* Noel

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 5

noel: Congratulations, I hope you and sp_help_job are very happy together. For some reason, it just didn't click with us. It's probably my fault, I tend to expect a lot from system stored procedures, and am often disappointed. We just installed a new development server, and, not that I'm hung up on material issues, but maybe that had some part in it. But who can say? I am too far down the development schedule to revisit this issue. I wish you both the best.

There is no "i" in team, but idiot has two.

john-907578 SSC Enthusiast Points: 120 More actions · Answer 6

I can't find sp_help_job, or any of the Agent SP's, under my SQL Server 2005's list of system stored procedures. How do I access sp_help_job?

Greg Jennings SSC Enthusiast Points: 175 More actions · Answer 7

Greg Jennings

SSC Enthusiast

Points: 175

May 7, 2009 at 2:35 pm

#990693

Use msdb..sp_help_job