none
Best method for calling stored procedures asynchronously (Threading, SQL Jobs.. ?)

    Question

  • Hello

    I need to start a stored procedure when clicking on a button.
    Depending on values in the database this procedure can take from milliseconds to seconds or even minutes.
    At the moment this procedure is definied in the dataset designer an executed by a button-click event.
    There are two problems with this:
    1. The default timeout is 30 seconds which causes a timeout exception sometimes.
    2. The whole application isn't multi-threaded, which means that everything hangs during the execution of the procedure.

    I've found two approaches for solving this problems:
    1. I could write a partitial class and change the timeout. In addition I could call the method asynchronously.
    Article describing how to call functions asynchronously: http://www.codeproject.com/KB/cs/AsyncMethodInvocation.aspx

    Problems: I don't have any experience in threading.  What happened if the user would close the application before the procedure finishes?
    Are there other threading-related problems I don't know?

    2. I could define a SQL Job and start it from my application.
    As far as I know this should solve both problems. Unfortunately I don't know if it works with SQL-Server 2000 and I don't know if this is a recommanded approach.
    Article describing this approach: http://blog.the-dargans.co.uk/2007/02/executing-job-on-sql-2005-server-in-c.html


    So what do you recommend me? Do you have other (better) solutions for this problem?

    Thank you in advance!

    • Moved by Bob BeaucheminMVP, Editor Wednesday, February 10, 2010 3:18 PM Moved to a more appropriate group (From:.NET Framework inside SQL Server)
    Wednesday, February 10, 2010 1:20 PM

Answers

  • Bef,

    I am sorry but in my opinion you are trying to complicate the thing

    In my past I had a request which involves merging word documents and creating PDF document out of it, the process could take about five to seven minutes.

    What I ended up is creating a web service and made a call to that web service asynchronously from client application.

    You put SoapDocumentMethod(OneWay = true)]  in your service (ASMX or WCF service), that way your application does not wait it calls the service, service does processing (whatever business logic you implement in your service)

    IF creating a web service is not possible then

    you can call a method asynchronously in your client code, have that method call a stored procedure

    http://support.microsoft.com/default.aspx/kb/315582
    Thursday, February 11, 2010 3:40 PM

All replies

  • There are two problems with this:
    1. The default timeout is 30 seconds which causes a timeout exception sometimes.

    You can increase the command object's command timeout value from 30 seconds to something reasonable say 300 seconds

    If that is still not acceptable,

    a) You can call stored procedure asynchronously, if you handle all exception gracefully no issues using async method

    b) You may want to research on using SQL Server notification services, it will be a learning curve if you have never used it but just another powerful option. 

     
    Wednesday, February 10, 2010 3:54 PM
  • Nothing works. :(

    This procedure could take so long that it shouldn't require the application to be running.
    Threads don't help with that problem.
    I need a way to just trigger the execution on the SQL-Server without having to wait for result.

    I've tried running the SQL-Job with this command:    
    exec msdb.dbo.sp_start_job N'Jobname';
    Unfortunately this works only if the user is member of the sysadmin fixed server role. I can't use this solution because It's a security problem. (I hate SQL-Server 2000!)

    Writing a service just for solving this problem is overkill in my opinion.
    Looks like the only solution that makes sense is scheduling the job... but I'm not happy with that.
    I don't like scheduling something if I could trigger it with a button.

    Is there really no other way?

    Thursday, February 11, 2010 10:09 AM
  • Bef,

    I am sorry but in my opinion you are trying to complicate the thing

    In my past I had a request which involves merging word documents and creating PDF document out of it, the process could take about five to seven minutes.

    What I ended up is creating a web service and made a call to that web service asynchronously from client application.

    You put SoapDocumentMethod(OneWay = true)]  in your service (ASMX or WCF service), that way your application does not wait it calls the service, service does processing (whatever business logic you implement in your service)

    IF creating a web service is not possible then

    you can call a method asynchronously in your client code, have that method call a stored procedure

    http://support.microsoft.com/default.aspx/kb/315582
    Thursday, February 11, 2010 3:40 PM
  • You could have a SQL Agent job that runs every minute. The job will execute a command that checks a table to determine whether or not to execute the stored procedure. Your application will populate that table. For example, the SQL Job step command will be something like

       DECLARE @RunJobFlag CHAR(1)
       SELECT @RunJob = RunJobFlag FROM tRunJob
       IF @RunJob = 'Y'
       BEGIN
         UPDATE tRunJob SET RunJobFlag = 'N'
         EXEC ProcedureToRun
       END

    and in your application, when the user clicks the button, you execute this T-SQL

       UPDATE tRunJob SET RunJobFlag = 'Y'

    Wednesday, June 22, 2011 11:48 AM