Best Method to Spawn Process from SQL Server Trigger

Question

How would I go about spawning a separate process using a SQL Server 05/08 trigger? Ideally, I would like to spawn the process and have SQL Server not wait on the process to finish execution. I need to pass a couple parameters from the insert that is triggering the process, but the executable would take care of the rest.

Answer 1

you want to use the system stored procedure xp_cmdshell

info here: http://msdn.microsoft.com/en-us/library/ms175046.aspx

Answer 2

I saw that particular article, but didn't see an option to 'spawn and forget'. It seems like it waits for the output to be returned.

xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

Answer 3

a bit of CLR Integration, combined with SQL Service Broker can help you here.

http://microsoft.apress.com/feature/70/asynchronous-stored-procedures-in-sql-server-2005