SQL Server: Kill process running xp_cmdshell

In our last post we discussed how to run a VB Script from SQL Server. Here I will share something interesting related to that task. Well, while extending the program logic of my VB Script I have used some message boxes to check the flow of some variable values. I also ran that script from command line, that finished successfully.

Now, when I try it to run my TSQL code (which uses xp_cmdshell to run that script) and observed that it is taking an unexpected time to complete !!!  After sometime I think of killing the process and killed, but still the query was running where I got a Success message from KILL !!!

Here is the reason behind that…after testing my script I forgot to remove/comment the message boxes, which cause my TSQL to run for an indefinite time.

Solution:

• The script or application we are calling from TSQL (xp_cmdshell) should be self-sufficient, i.e. application should not have any user interaction like InputBox or MessageBox, Visual Windows…etc.
• To stop the query which call such applications, we need to end the process from windows Task Manager.

Note: xp_cmdshell always run the target application in background and user can’t end it interactively to regain the control from that application.

More information for this topic is available here on Microsoft Support site or refer Book Online. Hope this helps you.

lated to

Advertisement