Calling xp_cmdshell from Trigger

[fogal]

Using a trigger I am calling this:
exec Master..xp_cmdshell 'c:\temp\temp.bat', no_output
Temp.bat contains the following :
@echo on
calc.exe
exit
'This will close temp.bat, but leave calc.exe open.

This is an example but calc.exe will be replaced with other .exe. However, Trigger still waits for calc.exe to be closed before freeing sql server. I do not want sql to wait for .exe to be closed, I want sql to continue to next record.

All ideas welcome!!

 

[psprague]

Generally, doing something like this is considered a bad idea. (It would be nice if sql server exposed events better but alas, wait for the next version....) As you have discovered, launching a process (an .exe) from with in sql server is a syncronous operation, meaning the calling thread is blocked until the process returns. Furthermore, if the process fails, it can crash sql server. If you really want to persue this, consider a extended stored procedure that has both fault tolerance and asyncronous thread handling.