如何从 SQL 运行程序？

How to run a program from SQL?

提问by LikeToCode

I need to implement a tool that runs programs from SQL Server database when a DB record matches some condition. Is there a way to do it?

我需要实现一个工具，当 DB 记录匹配某些条件时，该工具从 SQL Server 数据库运行程序。有没有办法做到这一点？

回答by Ron Skufca

I would be careful with xp_cmdshell because it can create quite a security hole. Check out this article from http://www.databasejournal.com/features/mssql/article.php/3372131/Using-xpcmdshell.htm

我会小心使用 xp_cmdshell，因为它会造成相当大的安全漏洞。从http://www.databasejournal.com/features/mssql/article.php/3372131/Using-xpcmdshell.htm查看这篇文章

If the service account has local administration rights then you can use this procedure to perform any windows operating system command.

如果服务帐户具有本地管理权限，则您可以使用此过程执行任何 Windows 操作系统命令。

Check out this similar question I asked some time ago. After some research I decided the security risk was too great for a production DB server. Your situation might be different and xp_cmdshell might be the answer. SQL Server xp_cmdshell

看看我前段时间问的这个类似的问题。经过一些研究，我认为安全风险对于生产数据库服务器来说太大了。您的情况可能有所不同，而 xp_cmdshell 可能就是答案。 SQL Server xp_cmdshell

回答by onupdatecascade

xp_cmdshell is a security problem, and also a design problem (making SQL Server run other apps is not really a happy circumstance, even if it can be forced to do so). And please, please don't take the trigger suggestion - that's the worst idea. What I would do first is modify, or create a wrapper for, the program you want to run, such that it polls the database for qualifying rows, and then works with them. If necessary make a table in the db to act as a queue for the rows to be processed, or a column to indicate rows where the process is complete.

xp_cmdshell 是一个安全问题，也是一个设计问题（让 SQL Server 运行其他应用程序并不是一个真正令人愉快的情况，即使它可以被迫这样做）。拜托，请不要接受触发建议 - 这是最糟糕的主意。我首先要做的是修改或创建要运行的程序的包装器，以便它轮询数据库以查找符合条件的行，然后使用它们。如有必要，在 db 中创建一个表作为要处理的行的队列，或者作为一个列来指示处理完成的行。

回答by Tetraneutron

You can start a program using xp_cmdshell

您可以使用 xp_cmdshell 启动程序

http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx

回答by Remus Rusanu

Running a process from a trigger is a dubious practice. Your transactions will block until the process is launched and finishes. In addition the process is guaranteed not be able to safely read the very record it has caused it to be launched, since is locked in a transaction that waits for the process to terminate.

从触发器运行进程是一种可疑的做法。您的交易将被阻止，直到该流程启动并完成。此外，进程被保证不能安全地读取它导致它启动的记录，因为它被锁定在等待进程终止的事务中。

The proper way to do this is detect the condition in the trigger, enqueue a notification and have a post-commit reader dequeue the notification and launch the process. All this can be done in Transact-SQL.

执行此操作的正确方法是检测触发器中的条件，将通知加入队列，并让提交后的读取器将通知移出队列并启动进程。所有这些都可以在 Transact-SQL 中完成。

Update

更新

BTW when I say 'is all doable in Transact-SQL' I'm referring to Service Broker and activation: the trigger SENDs a message to a local service, perhaps describing the key of the item updated that matched the criteria, after commit the message activates the procedure, the activated procedure reads the message and launches xp_cmdshell to run the tool with proper parameters. The activated procedure has the rights to run xp_cmdshell because is properly signed. The tool is run in a matter of milliseconds after the update, but in from a perfectly safe context that does not block the update/insert/delete.

顺便说一句，当我说“是在Transact-SQL的所有可行的”我指的是服务代理和激活：触发SENDSA消息的本地服务，或许说明更新匹配的标准，提交信息后该项目的关键激活程序，激活的程序读取消息并启动 xp_cmdshell 以使用适当的参数运行该工具。已激活的程序有权运行 xp_cmdshell，因为已正确签名。该工具在更新后几毫秒内运行，但在一个完全安全的上下文中运行，不会阻止更新/插入/删除。