title: MSSQL Run stored procedure multiple times concurrently
body: <p><strong>The Problem</strong></p>
  <p>I have a CakePHP application in which I call a stored procedure:</p>
  <pre><code>$this-&gt;FileUpload-&gt;query('exec sproc_runjob_ProcessTests', false);
  </code></pre>
  <p>This procedure picks up an uploaded file and processes it. Sometimes people upload multiple files, which calls this once for each file uploaded.</p>
  <p>The problem is that I'm getting this error:</p>
  <pre><code>Database Error
  Error: SQLSTATE[42000]:
      [Microsoft][SQL Server Native Client 11.0][SQL Server]
      SQLServerAgent Error: Request to run job ProcessTests
      (from User [redacted]) refused because the job already has
      a pending request from User [redacted].
  SQL Query: exec sproc_runjob_ProcessTests
  </code></pre>
  <p>As far as I can tell, this means that the stored procedure can't run concurrently - I would have to run the sproc for each process serially, which would be a shame on our fancy new multi-core server.</p>
  <p><strong>Solutions we've thought of</strong></p>
  <ol>
  <li><p>Run the sproc serially</p>
  <p>Basically the idea here is to call the stored procedure once, then ignore it's failure if it's already running. At the end of the sproc call, it would check the database to see if it needs to run again (if there are any &quot;new&quot; files) and run itself again if that is the case.</p>
  </li>
  <li><p>Don't use the existing stored procedure</p>
  <p>This would be difficult because of time restrictions, but if someone has a good suggestion for the way we <em>should</em> be doing this, I'd be happy to hear it.</p>
  </li>
  </ol>
  <p>Thanks in advance!</p>
  <p><strong>Search results</strong></p>
  <ol>
  <li><p>Looks like <a href="http://www.sqlservercentral.com/Forums/Topic1033621-146-1.aspx" rel="nofollow noreferrer">this guy</a> has the same problem, and solves it by checking the job status before calling. Perhaps I could do this from PHP before I make the sproc call?</p>
  </li>
  <li><p><a href="https://stackoverflow.com/questions/5861255/how-to-accurately-detect-if-a-sql-server-job-is-running-and-deal-with-the-job-al">Here's a mention</a> of something called Service Broker. This might actually be what I'm looking for, but I'd still love any input.</p>
  </li>
  </ol>
  <p>Thanks in advance!</p>