CODEWITHSUNDEEP
asp.netsqlsql-servervb.netsql-server-2008-r2
user194076
user194076

Reputation: 9017

Easy way to run procedure in the background

I'm running stored procedure from asp.net front-end but it's quite long. What is the easy way on running that thing in the background? I mean if I close the browser, I still want my stored procedure to complete not just die. Also, I would like to perform other actions on the front-end while my procedure is running. Any good solution for that?

Upvotes: 2

Views: 2066

Answers (3)

evpo
evpo

Reputation: 2531

Use "Asynchronous Processing=true" in your connection string and call the sproc asynchronously as in this link.

http://geekswithblogs.net/frankw/archive/2008/07/05/execute-transact-sql-statement-asynchronously.aspx

Upvotes: 0

Icarus
Icarus

Reputation: 63956

Just launch it in another Thread as so:

'Starts execution of the proc
 Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        Dim t As New Threading.Thread(AddressOf DoWork)
        t.Start()
    End Sub

 Private Sub DoWork()
        Using c As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
            c.Open()
            Dim command = New SqlCommand("LongTest", c)
            command.CommandType=Data.CommandType.StoredProcedure
            command.CommandTimeout = 0
            command.ExecuteNonQuery()
        End Using
    End Sub

Here's the sp that I used for my test:

create PROCEDURE dbo.LongTest
AS
BEGIN
   WaitFor Delay '00:00:30' --wait for 30 seconds before doing anything
   insert into TableImageTest(image)
   values(null)

END

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

Both SQL Agent and the Service Broker can do this, though it does take some work on your part.

Upvotes: 2

Related Questions