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?

Answer 1

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.