表更新触发异步后从 SQL Server 运行控制台应用程序？
我无法找到解决问题的最佳方法，请记住，我愿意接受更好的方法来完成此任务。
我需要做的是，在更新表中的行值后，我需要使用该表中的 2 个字段作为控制台应用程序的参数。现在，我可以通过在表上设置触发器，然后使用 xp_cmdshell 使用参数运行应用程序来完成此操作。但是，我需要异步执行此操作，以便我的存储过程在等待控制台应用程序完成时不会挂起。
也许我的处理方式是错误的。
我正在使用 SQL Server 2008
编辑 - Andriy M 的答案似乎是目前最好的，但正如评论中所述，我需要一种方法来“立即”实现这一点。是否可以从 SP 或触发器调用作业？或者也许有另一种方法可以达到类似的结果？
感谢大家的帮助。
编辑-我选择下面的答案，因为它帮助我找到了更好的解决方案。我最终要做的是创建一个作业，该作业仅针对另一个跟踪更新行的表查询我的表。然后，当我有需要更新的行时，我使用 xp_cmdshell 使用指定的参数运行我的应用程序。到目前为止，该解决方案似乎运行顺利。
I am having trouble with finding the best way to solve my issue, please keep in mind I am open to better ways of going about this task.
What I need to do is, after a row's value in my table is updated, I need to use 2 fields from that table as parameters for a console application. Right now I can accomplish this by setting a trigger on the table and then using xp_cmdshell to run the application with the parameters. However I need to do this asynchronously so my stored procedure doesn't hang while it waits for the console application to finish.
Maybe I am going about this the wrong way.
I'm using SQL Server 2008
EDIT - The answer by Andriy M seems to be the best currently but as stated in the comments I need a way to make this happen "Instantly". Is it possible to call a job from a SP or a Trigger? or maybe another way to achieve a similar result?
thanks for the help everyone.
EDIT - I choose he answer below because it helped me the most come to a better solution. What i end up doing was create a job that just queries my table against another which keeps track of updated rows. then when i have the rows i need to update i use xp_cmdshell to run my application with the specified parameters. this solution appears to be working smoothly so far.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
直接从触发器运行应用程序还有另一个缺点。这与以下事实有关：通常可以更新多行。为了在触发器中考虑到这一点，您可能必须在更新的行上组织一个循环，并为每个单独运行应用程序。游标通常被认为是最后的手段，而触发器中的游标更是如此。
在这种情况下，我很可能会考虑创建一个 SQL 代理作业，该作业将从由触发器填充的专用表中读取更新的值。我认为该作业仍然必须使用光标，但您的触发器不会，而且要点是，从作业运行应用程序不会停止您的主要工作流程。
There is another disadvantage to running your application directly from the trigger. It has to do with the fact that generally there can be more than one row updated. To account for that in your trigger, you'd probably have to organise a loop over the updated rows and run the application for each individually. Cursors are typically considered as a last resort, and those in a trigger even more so.
In a situation like this I would most probably consider creating a SQL Agent job which would read the updated values from a dedicated table populated by a trigger. The job would still have to use a cursor, I think, but your trigger wouldn't, and the main point is, running the application from the job wouldn't stop your main working process.
在触发器中，为更新的每一行将一条消息放入 Service Broker 队列中。编写一个存储过程来处理队列中的消息。将存储过程设置为队列的激活存储过程。
In your trigger, put a message onto a Service Broker queue for each row that got updated. Write a stored procedure that processes messages off of the queue. Set the stored procedure as the activation stored procedure for the queue.
我认为您应该开发一个扩展存储过程（DLL），而不是使用 xp_cmd_shell 调用控制台应用程序。
I think you should develop an extended stored procedure (DLL) instead of calling a console application using xp_cmd_shell.
我建议使用 CLR 过程，因为它可以让您更好地控制该过程。但是您可以使用
xp_cmdshell来做到这一点。为此，您可以编写一个批处理文件，并使用
xp_cmdshell调用该文件。在批处理文件中，使用START命令使用正确的参数启动控制台应用程序。这将异步启动您的进程。批处理文件和xp_cmdshell调用将立即返回。I would suggest using a CLR procedure, as it gives you much more control over the process. But you can do this with
xp_cmdshell.To do this you can write a batch file that you will invoke with
xp_cmdshell. Inside the batch file, kick off the console application with the proper parameters using theSTARTcommand. This will fire off your process asynchronously. The batch file, and thexp_cmdshellinvocation, will return immediately.