从C＃中触发并忘记Sql Server存储过程

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.

我正在排队工作,在每项工作结束时,我想要启动一个SP,它将对数据进行大量处理。所以我不想等待SP的完成,我只是想在触发SP之后立即转到下一个工作。存储过程将从触发代码中获取输入。

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?

现在,当我通过执行EXEC msdb.dbo.job_JobName'开始这项工作时,它没有执行dbo.SpToExecute。我需要在作业中运行一次dbo.SpToExecute,然后作业应该停止。再次当我执行EXEC msdb.dbo.job_JobName'时,它应该再次运行exec dbo.SpToExecute一次。你能告诉我如何实现这个或我在这里缺少的东西吗?

3 个解决方案

#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.

您可以使用BeginExecuteNonQuery或其他异步方法,或者您可以创建将运行SP的SQL作业,然后只需调用常规同步ExecuteNonQuery来启动作业。它将立即返回,因为开始工作很快。然后工作运行,你可以“忘记”它。

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

这是SQL代理作业方法的一些解雇和遗忘代码。

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

string sql =“EXEC dbo.sp_start_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.

然后在数据库上执行该操作。它应该立即返回。如果成功则返回0,如果不成功则返回1。看这里。

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.

您无法将参数传递给作业。因此,如果需要,您可以创建一个表来保存您想要传递的参数。然后,在调用sp_start_job SP之前,需要使用希望SP使用的参数更新表。然后,您还需要SP查看该表并查看要使用的参数。这很简单。

That's about it. Fire and forget.

就是这样。火与忘。

#2

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

您似乎希望使用SQL Server T-SQL处理所有这些,而不是通过.NET代码。

If so you might study this implementation

如果是这样,您可以研究这个实现

#3

What you need is ASYNCHRONOUS PROCEDURE EXECUTION. This solution is based on Service Broker (link 1, link 2, link 3).

您需要的是异步程序执行。此解决方案基于Service Broker(链接1,链接2,链接3)。

Why this solution is better ?

为什么这个解决方案更好?

Remus says: August 10, 2009 at 10:06 am I don’t like SQL Agent in these scenarios for 3 reasons:

Remus说:2009年8月10日上午10:06我在这些场景中不喜欢SQL Agent有三个原因:

– it does not have the self load-balancing capabilities of Service Broker activation.

- 它没有Service Broker激活的自我负载平衡功能。

– is difficult to take a consistent backup because of the distribution of metadata across user database and msdb.

- 由于跨用户数据库和msdb分发元数据,很难进行一致的备份。

– It is not available in Express editions.

- Express版本不提供此功能。

#1