SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Posts Tagged ‘xp_cmdshell’

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 »

Get List Of Files From a Windows Directory to SQL Server

Posted by Prashant on June 8, 2010


In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.

So in order to use xp_cmdshell, we need to enable it as it is disabled by default.

Here is the way to enable:

--allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--Update the currently configured value for advanced options.
RECONFIGURE
GO
--Enable XP_CMDSHELL
EXEC sp_configure 'xp_cmdshell', 1
GO
--Update the currently configured value for this feature.
RECONFIGURE
GO

Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:

(Image-1)

--Create the table to store file list
CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))
--Insert file list from directory to SQL Server
DECLARE @Path varchar(256) = 'dir C:\Import\'
DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B'
INSERT INTO myFileList
EXEC MASTER.dbo.xp_cmdshell @Command
--Check the list
SELECT * FROM myFileList
GO
--Clean up
DROP TABLE myFileList
GO

Here is the result in the table.

This can be extendable to other file operations too. Leave your valuable comments and suggestions.

Posted in General, SQL Server | Tagged: , , , , | 10 Comments »

 
%d bloggers like this: