Hello all,
I run queries using SQLCMD utility via PHP and I wanted to know if there was a way to stop queries or scripts that I have executed via another SQL command by passing the process ID or something.
I also make use of the PHP Driver for SQL Server would it be easier to do this way?
Or is none of this possible - once a query runs you can not stop it?
Thanks all
Depends how you're calling that SQLCMD. Are you running it as a call to exec()? The PHP commands don't give you a lot of control.
If you're doing something time-consuming, it might be better to use a Producer-Consumer architecture. Have your main program publish (to a database, or a text file) the command to be run. Have a separate process (another PHP script, perhaps) run that command - but before it starts the SQLCMD, have it provide its process ID (or some other handle) to the main program.
Then, in your main program, you can kill the spawned process if it is taking too long.
Some of the comments on the PHP exec function might be enlightening: http://au2.php.net/manual/en/function.exec.php
SQL Server keeps track of all executing processes. You can review the list by using stored procedures SP_WHO and SP_WHO2 - each process has a unique SPID.
In the past, I have copied and customized the code behind these procedures for my own purposes.
You can check the query last executed on a SPID using DBCC INPUTBUFFER (@SPID)
You can kill most processes using KILL @SPID
Do note that it is not generally considered a good idea to kill processes because you may not be sure about exactly what they are doing.