Store Procedure in Thread (ASP.NET)

Posted on 2004-11-03
Last Modified: 2010-04-17

I have a store procedure that can take like 10 - 15 minutes.
I want to execute the SP in a Thread, because the user can cancel the query.

I try with Dim MyThread As New Thread(AddressOf runQuery)

So i put a start button (MyThread.Start()) an a cancel but (MyThread.Abort(),, i guest...)

My query runs fine, but i can't cancel de thread..

I'm using ASP.NET, with a SQL Server 2000 DB. And a WebService have the logic of the app...

WebForm -> WService -> VB Class -> DB

Any help please?
Question by:Alucard_cr
    LVL 3

    Expert Comment


    Cancelling a thread that executes a stored procedure is a difficult task as the SP is run in a different process that is controlled by the DB server. So although the "thread" that started the execution of the thread the procedure would still execute.

    However to know the inner workings of a thread.abort(), what happens is on calling abort it just raises a thread abort exception that is raised again after your catch block has executed. but again the finally block is excuted and then the thread is stopped.

    so this is what you could do:

    put a try catch block in the method that the thread would execute.

    add a thread.join() immediately after the thread.abort() call. this would block your current thread until the aborted thread has stopped.

    however just to let u know.. that wont abort the SP executing


    Author Comment

    Thanks vippx, I'll do that and try a little more.

    I'll wait for more comments...

    Thank you
    LVL 3

    Expert Comment

    Closing the database connection should inform the database server to cancel executing the stored procedure.

    Author Comment

    I'll try to cancel the query. Maybe a Command.Cancel() or with the DataAdapter...??
    LVL 3

    Accepted Solution

    Yes ... the command.cancel() should cancel the command. but make sure that appropriate rollbacks are in place. too often, killed SPs are real messy

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Add mobile access to browser application 3 106
    FizzBuzz challenge 9 64
    maxMirror challenge 10 67
    scores100 challenge 3 66
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now