SQL Agent Job - T-SQL Job Step Question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Agent Job - T-SQL Job Step Question

SQL Agent Job - T-SQL Job Step Question

Rate Topic

Display Mode

Topic Options

Author

Message

Brandie Tarvin

Posted Monday, January 07, 2013 12:04 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,668, Visits: 5,695

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 Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1403800

opc.three

Posted Monday, January 07, 2013 3:47 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 6,730, Visits: 11,784

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1403898

MyDoggieJessie

Posted Monday, January 07, 2013 8:41 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1403951

Brandie Tarvin

Posted Tuesday, January 08, 2013 4:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,668, Visits: 5,695

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 Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1404144

« Prev Topic | Next Topic »

Permissions