views:

211

answers:

1

I have a client application that scrapes data into a raw table. This client application makes an asynchronous call to a stored procedure that actually parses the raw data into tables. This processing is handled by a stored procedure. That stored procedure can take up to a half hour to run. We want the client application calling the stored proc to exit and completely stop running. As soon as we kill the client application that calls the stored proc the stored procedure quits executing.

What is the best way to acheive this? I do not want to run a job on the server. I want the client application to trigger this processing. A database trigger (my first idea) didn't solve the problem either because it doesn't consider the insert complete until the stored procedure the trigger is calling is complete.

Here is the Async method. I'm using LINQ. Can I somehow modify this? Or do I need a new design altogether?

partial class MetaDataContext
{

    delegate int Process_CompleteCycle2Delegate(int? frequencyID, int? cycleID);

    public void Process_CompleteCycle2Async(int? frequencyID, int? cycleID)
    {
        Process_CompleteCycle2Delegate completeCycleDelegate = new Process_CompleteCycle2Delegate(this.Process_CompleteCycle2);
        IAsyncResult async = completeCycleDelegate.BeginInvoke(frequencyID, cycleID, null, null);

}
+2  A: 

See Asynchronous procedure execution for a reliable and transactionally consitent way.

Remus Rusanu
perfect...thanks Remus
ctrlShiftBryan
is there any reason why a when I call my stored proc with your method I get lots of SPIDs blocking each other? When I run my proc otherwise either through SSMS or through my application this doesn't happen.
ctrlShiftBryan
Not in particular. The extra conversation/queue infrastructure needed adds some more locks, but they should not conflict (no blocking). The execution background also adds some locks, but again, it should not conflict. Can you use sys.dm_exec_requests to see what is the contention on? What statements are blocking each other, on what resource). If you preffer to take this offline, use the contact form on my site and send me more details. I'll be able to look over them later today.
Remus Rusanu