title: Executing a time consuming stored procedure on ASP.NET 2.0 + Sql Server 2008
body: <p>In one part of our application, we have to save some data. The saving of data takes some time and it could vary from few secs to few minutes. To handle this, so there isn't a sql time out we have following process in place.</p>
  
  <p><strong>Note</strong>: This is all setup by our DBA and this process is in use for several years without any problem.</p>
  
  <p><strong>1:</strong> Call a storedProcedure that places a job in a table. This stored procedure returns me a jobid. The input parameters to this sp is an exec statement to execute another storedprocdure with parameters.</p>
  
  <p><strong>2:</strong> Then I call another stored procedure passing the jobid, that will return the status of the job e.g. completed(true) else false + an error message.</p>
  
  <p><strong>3:</strong> I loop through step 2 for max 5 times with a sleep of 2 seconds. If the status returned is completed or there is some error, I break out of the loop. And after 5 times still the status in incomplete, I just display message that it's taking too long and please visit the page later (this isn't a good idea but so far the process has finished in about 2-3 times looping and not reached 5 times max but there are chances).</p>
  
  <p>Now I am wondering if there is any better way to do the above job or even today, after several years, this is the best option I have?</p>
  
  <p>Note: Using ASP.NET 2.0 Webforms / ADO.NET</p>