SQL Agent Job - T-SQL Job Step Question

Question

SQL Agent Job - T-SQL Job Step Question

Brandie Tarvin

SSC Guru

Points: 172995
More actions
January 7, 2013 at 12:04 pm

#287300

I am sure I read somewhere that when executing other jobs from the steps of one job, that the main job just kicks off the other job from the job step, then continues to the next main job step without waiting for the junior job to finish. Now I'm trying to determine if the same is true for stored procedures, but I can't find the original bit in BOL (or online) where I saw this information.
Does anyone have any links (or BOL keywords) that point to what I'm talking about? Or, if I'm totally misremembering this, please point me to links stating the opposite.
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

It sounds like you're referring to msdb.dbo.sp_start_job which returns control to the caller immediately after submitting the request to SQL Agent to start the job reporting 1 or 0 as its return code signifying whether it could start the job or not, i.e. it does not wait for the job to complete so the return code says nothing about whether the job succeeded or failed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

With jobs, it works (as each job/job step is a separate "batch", however when executing stored-procedures within the same session SQL processes every batch sequentially (to adhere to ACID) and I believe the only way around it is to do it using OLE automation.

I can't find anything in BOL but did find a good article (SQL2005+) by Greg Larson on how to do this using sp_oacreate (which creates a wscript.shell object to execute the parallel code/procedure:

http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2/Submitting-A-Stored-Procedure-Asynchronously.htm

He points out some of the obvious issues with doing this type of thing. Not a bad article...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 3

Thanks. I appreciate the answers. Now I need to figure out why it seems to the BU that they are running asynchronously.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.