SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for August, 2010

SQL Server: Kill process running xp_cmdshell

Posted by Prashant on August 6, 2010


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

Posted in Operating System, SQL Server | Tagged: , , , , | Leave a Comment »

SQL Server: Running VB Script From TSQL

Posted by Prashant on August 3, 2010


In this post we will see if we can run a VB Script from SQL Server.  Yes, SQL Server allows to run external Scripts. So, here is the process how to run a VB Script from TSQL Code.

Well to test this lets first create a sample VB Script which will just display a message.

Dim VarSetMessage
VarSetMessage = "Running VB Script from SQL Server..."

'Display message
WScript.Echo VarSetMessage

Save it as “myMsg.vbs” on “C:\” drive of your machine. Okey, before running this script from SQL Server, lets see how to run it from Command Prompt. To do this:

Start => Run => cmd => ENTER
Type Cscript C:\myMsg.vbs => ENTER

(Refer below image for result)

Now to run the VB Script from SQL Server just run the same command line we tested above using xp_cmdshell from SQL Server.

EXEC XP_CMDSHELL 'CScript C:\myMsg.vbs'

and we are done !!!

Note: You may need to configure xp_cmdshell if not configured before.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: