Friday, February 17, 2012

execute long running transaction - fire and forget

How can I execute a long running transaction using something similar to the fire and forget pattern?

I intend to start the execution of a very long stored proc from within IIS. I would like to execute a sql script that will start the job and return immediately so that it doesn't hold an IIS thread.

You can create an on-demand SQLAgent job that calls your SP and fire the job. See BOL for more details on how to create a SQLAgent job.

|||

Hi

Try something like that.

Philippe

Protected Sub RunJob()

' Dim rowCount As Integer

Dim previousConnectionState As ConnectionState

Dim conn As New SqlConnection("server=datamart;integrated security=true;" + "database=MSDB")

Dim cmd As New SqlCommand("msdb.dbo.sp_start_job @.job_name ='Distributor NPD Stocking data and cube'", conn)

previousConnectionState = conn.State

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

cmd.ExecuteNonQuery()

Finally

If previousConnectionState = ConnectionState.Closed Then

conn.Close()

End If

End Try

End Sub

No comments:

Post a Comment