Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First of all, have you had a chance to look at service broker? From your description, it sounds like that's what you actually want. </p> <p>The difference would be instead of starting a job, you put your data into a SB queue and SB will call your processing proc asynchronously and completely side-step issues with already-running jobs etc. It will auto spawn/terminate additional threads and demand dictates, it takes care of order etc. </p> <p>Here's a good (and vaguely related) tutorial. <a href="http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker" rel="nofollow">http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker</a></p> <p>Let's assume that you can't use SB for whatever reason (but seriously, do!).</p> <p>What about using the job spid's context_info. </p> <ol> <li>Your job calls a wrapper proc that execs each step individually.</li> <li><p>The first statement inside the wrapper proc is </p> <pre><code>DECLARE @context_info VARBINARY(30) SET @context_info = CAST('MyJob1' AS VARBINARY) SET CONTEXT_INFO @context_info </code></pre></li> <li><p>When your proc finishes (or in your catch block) </p> <pre><code>SET CONTEXT_INFO 0x0 </code></pre></li> <li><p>When you are looking at calling your job, do this:</p> <pre><code>IF NOT EXISTS (SELECT * FROM master..sysprocesses WITH (NOLOCK) WHERE context_info=CAST('MyJob1' AS VARBINARY)) EXEC StartJob </code></pre></li> </ol> <p>When your wrapper proc terminates or the connection is closed, your context_info goes away. </p> <p>You could also use a global temp table (i.e. ##JobStatus) They will disappear when all spids that reference it disconnect or if it's explicitly dropped. </p> <p>Just a few thoughts.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to accurately detect if a SQL Server job is running and deal with the job already running?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USCode Magician
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to accurately detect if a SQL Server job is running and deal with the job already running?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI did not know about the Context_info feature. Interesting, although, at then end of the day, that approach is basically the same thing as using a table, with rows containing the SPIDs of the jobs and some tracking info. Possibly a little simpler though (since the context info goes away automatically when the job finished.
      singulars
      1. PO
      1. USDarinH
    2. COI looked into ServiceBroker initially, but the client nixed that as they had no one that knew anything about it on staff. Still, the servicebroker stuff is definitely on my mind for similar future projects. Thanks!
      singulars
      1. PO
      1. USDarinH
    3. COI'm giving this answer the nod, mainly because 1) it mentions CONTEXT_INFO which i'd not used before, and 2) it is essentially what I ended up with using a Job Tracking table, although the Global Temp table might be a +little+ cleaner, in that it tends to clean up after itself automatically, whereas with a normal table, I had to make sure things got cleaned up explicitly.
      singulars
      1. PO
      1. USDarinH
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload