How to run a program from SQL?

Question

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?

Answer 1

You can start a program using xp_cmdshell

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

Answer 2

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.

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.

Answer 3

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

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

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. http://stackoverflow.com/questions/288084/sql-server-xpcmdshell

Answer 4

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.