Calling sp_start_job - 08-20-2009 , 12:51 AM
I have a fairly large stored proc that does some analysis of financial data
(returning about 2-3k rows) using about 10 different T_Sql queries and
writes the results of each to a temp table which I then save to another
table. This is the result of daily financial reconciliation for historical
purposes. This is called from our in-house VB.Net/C# application.
While I run this through a component class in the background, my thinking is
that it may be better to execute a Sql job using sp_start_job via ADO.Net
since I do not require any feedback on whether it completed. The failure of
the job will be emailed to me, if that occurs. This call has been used many
thousands of times in production and has never failed.
My question is:
Does/can a call from VB.Net to sp_start_job release immediately after making
the call or does it wait for the job to complete? My apologies for posting
this in two groups but either group could have an answer that is useful.
Thank you for your time
RE: Calling sp_start_job - 08-20-2009 , 03:29 AM
The sp_start_job system procedure only notifies SQL Server Agent to start the
job, then returns to the caller.
It does not wait for the job to complete, nor does it monitor job progress.
It makes a few checks *before* initiating the job, but nothing after the job
You will therefore need another way of monitoring job execution (e.g. using
Matija Lah, SQL Server MVP