Fire and Forget Sql Server Stored Procedure from C#

Question

I'm doing a queued jobs and at the end of each job I want to fire an SP which will do a lot of processing on data. So I don't want to wait for completion of the SP and I just want to move to next job immediately after triggering the SP. Stored procedure will take an input from the triggering code.

Problem:-
This is my script to create job. Notice that I've not added any schedule to it.

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'job_JobName', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'UserName', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StepName', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec dbo.SpToExecute', 
        @database_name=N'DataBaseName', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Now when I start this job by executing EXEC msdb.dbo.job_JobName', it is not executing dbo.SpToExecute. I need to run dbo.SpToExecute only once within the job and then job should stop. Again when I execute EXEC msdb.dbo.job_JobName' it should again run exec dbo.SpToExecute only once. Can you please tell me how to achieve this or what I'm missing here?

Take a look at [SqlCommand.BeginExecuteNonQuery](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.beginexecutenonquery.aspx) [SqlCommand.BeginExecuteReader](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.beginexecutereader.aspx) [SqlCommand.BeginExecuteXmlReader](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.beginexecutexmlreader.aspx) — Sanjeevakumar Hiremath, Mar 15 '11 at 06:29
Some other thread will have to wait for the result right? That is not fire and forget I suppose. Because I have lot of jobs in Queue, most of the threads will be in use by some of those jobs and so I want to free this finishing thread quickly, after assigning SQL Server (which resides on other box) it's task, to make it available for one of the jobs in queue. — IsmailS, Mar 15 '11 at 06:38

richard · Accepted Answer · 2013-01-25T16:38:46.317

14

You could use the BeginExecuteNonQuery or other asynchronous methods or you could create a SQL job that will run your SP(s) and then just call the regular synchronous ExecuteNonQuery to fire off the job. It will return immediately, since starting a job is quick. Then the job runs and you can "forget" about it.

Here's some fire and forget code for the SQL agent job approach.

string sql = "EXEC dbo.sp_start_job 'THE NAME OF YOUR JOB'";

And then just execute that on the database. It should return immediately. It will return a 0 if it was successful, and a 1 if unsuccessful. See here.

You can't pass a parameter to the job. So if you need to, you could create a table that would hold the parameters you want to pass. Then you would need to update the table with the parameters you want your SP to use before you call the sp_start_job SP. Then you would also need your SP to look in that table and see what parameters to use. It's pretty easy.

That's about it. Fire and forget.

edited Jan 25 '13 at 16:38

answered Mar 15 '11 at 06:50

richard

12,263
23
95
151

@Sanjeev: this is the only answer with these suggestions, and no comments suggest the SQL server job approach (which I have used successfully in the past). – richard Mar 15 '11 at 07:04
Right, I guess the tradeoff between comment and answer is that how much time and interest for a question. Agreed. – Sanjeevakumar Hiremath Mar 15 '11 at 07:07
Hopefully this answer will benefit others, so it needs to be a real answer so that it can get voted and accepted so others will know it is a good answer. Comments are helpful but don't finish the circle of question/answer. – richard Mar 15 '11 at 07:13
+1. Any code for firing the job and is there any authentic source which states "It will return immediately, since starting a job is quick. Then the job runs and you can "forget" about it."? – IsmailS Mar 15 '11 at 07:25
I will post some code tomorrow. I'm in bed on my phone at the moment. That's the extent of my SO addiction. :-P. As far as an authentic source I only know that I have done it many times and the code executes and returns as quickly as it can connect to the db and call the sp_start_job system stored procedure. – richard Mar 15 '11 at 07:36
Sure! Thanks! Also, will I be able to pass a parameter to the SQL job which in turn will pass it to the SP? – IsmailS Mar 16 '11 at 04:17
Edited the answer with some added code and instructions/suggestions. Good luck! – richard Mar 16 '11 at 08:32
@Ismail: Did the code work for you? Did this answer your question satisfactorily or is there something else you are wondering about? – richard Mar 20 '11 at 00:25
Right now I'm working on other areas. I'll try out your solution when I come to it again. – IsmailS Mar 21 '11 at 04:53
The thing is, I need to run the steps only once and immediately when I start the job. That's why while creating the job I've not set any schedule for it. So should it fire the steps immediately as I start the job using the SP you gave? Because it's not doing this. Any reasons? – IsmailS Jun 22 '11 at 15:11
@Ismail: I'm not sure I understand your question. What is happening? – richard Jun 22 '11 at 16:16
I've added the details in question. Thanks in advance. – IsmailS Jun 23 '11 at 08:58

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

It seems like you are looking to handle all of this with SQL Server T-SQL and not through .NET code.

If so you might study this implementation

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 15 '11 at 06:57

Simen S

3,210
18
24

Do you mean that I should implement all the threading thing (which I have already implemented) again in T-SQL for this bit of issue? ;) – IsmailS Mar 16 '11 at 04:19

Fire and Forget Sql Server Stored Procedure from C#

2 Answers2