SQL Server xp_cmdshell

 in SQL Server  tagged DOS / xp_cmdshell / command by

From within SQL Server you can spawn a command shell to the operating system. Therefore you can run operating system commands from within a sql script in SQL Server. You can also run it from a stored procedure. As Microsoft puts it on their website article on xp_cmdshell (Transact-SQL) “Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text”.

What is xp_cmdshell? xp_cmdshell is an extended stored procedure, which are programs written in languages like C++ that are then attached to an instance of SQL Server. Once attached, they can be referenced just like a regular stored procedure.

xp_cmdshell is disabled by default. Use sp_configure or Policy Based Management to enable it. This Microsoft article xp_cmdshell Server Configuration Option shows you the code to run to enable the use of xp_cmdshell. 

Msg 50000, Level 16, State 1, Procedure dbo.usppb_0000Postback, Line 135 [Batch Start Line 2]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Below is the code to configure it. This code is from the above mentioned website. 

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

Caution

A cautionary note from Steve Jones over at SQL Server Central in the article called Return Values from XP_CMDSHELL: “I don’t recommend the use of xp_cmdshell as a general tool. It ought to be used when you have no alternatives, and you should carefully control access and what this can do. Opening a shell from SQL Server can be dangerous for your server”.

Consider the following code in SQL Server. 

EXEC master..xp_cmdshell 'md c:\newdir';  -- make new directory
EXEC master..xp_cmdshell 'dir c:\newdir';  -- get directory listing
EXEC master..xp_cmdshell 'type NUL > c:\newdir\1.txt';  -- create a new file
DECLARE @cmd sysname;  -- sysname is similar to nvarchar(128) NOT NULL
DECLARE @newfile sysname;
DECLARE @newdir sysname;
SET @newdir = 'c:\newdir\'
SET @newfile = '2.txt'
SET @cmd = 'type NUL > ' + @newdir + @newfile
EXEC master..xp_cmdshell @cmd;  -- create another file

Note that we referenced the master database in the EXEC statement. To execute an extended stored procedure that starts with “xp_”, either the master database must be active or referenced by EXEC.

Read a List of Files in a Folder

From within SQL Server you can read in a list of files in a folder. We need to have a look at the DOS command dir. Open a command prompt and type help dir. You will see something like the following. 

Displays a list of files and subdirectories in a directory.

DIR [drive:][path][filename] [/A[[:]attributes]] [/B] [/C] [/D] [/L] [/N]
  [/O[[:]sortorder]] [/P] [/Q] [/R] [/S] [/T[[:]timefield]] [/W] [/X] [/4]

  [drive:][path][filename]
              Specifies drive, directory, and/or files to list.

  /A          Displays files with specified attributes.
  attributes   D  Directories                R  Read-only files
               H  Hidden files               A  Files ready for archiving
               S  System files               I  Not content indexed files
               L  Reparse Points             -  Prefix meaning not
  /B          Uses bare format (no heading information or summary).
  /C          Display the thousand separator in file sizes.  This is the
              default.  Use /-C to disable display of separator.
  /D          Same as wide but files are list sorted by column.
  /L          Uses lowercase.
  /N          New long list format where filenames are on the far right.
  /O          List by files in sorted order.
  sortorder    N  By name (alphabetic)       S  By size (smallest first)
               E  By extension (alphabetic)  D  By date/time (oldest first)
               G  Group directories first    -  Prefix to reverse order
  /P          Pauses after each screenful of information.
  /Q          Display the owner of the file.
  /R          Display alternate data streams of the file.
  /S          Displays files in specified directory and all subdirectories.
  /T          Controls which time field displayed or used for sorting
  timefield   C  Creation
              A  Last Access
              W  Last Written
  /W          Uses wide list format.
  /X          This displays the short names generated for non-8dot3 file
              names.  The format is that of /N with the short name inserted
              before the long name. If no short name is present, blanks are
              displayed in its place.
  /4          Displays four-digit years

Switches may be preset in the DIRCMD environment variable.  Override
preset switches by prefixing any switch with - (hyphen)--for example, /-W.

If we want only the files that are in the directory c:\ we use the /a and the /b switch. Here is some SQL code that does this and stores the results into a table variable. We can then copy that into a permanent table if needed. The -d means not directories (only files). 

declare @filesonly table (ID int IDENTITY, FileName varchar(100))
insert into @filesonly execute xp_cmdshell 'dir c:\ /a:-d /b'
select * from @filesonly

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. Below is the syntax, from w3schools.com. 

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

You could extend this code to include the adding of the list to your permanent table. 

-- You have a table called MyFileList with column called FileName
declare @filesonly table (ID int IDENTITY, FileName varchar(100))
insert into @filesonly execute xp_cmdshell 'dir c:\ /a:-d /b'
-- show the list...
select * from @filesonly where FileName IS NOT NULL
-- insert into a permanent table...
INSERT INTO [dbo].[MyFileList] ([FileName])
SELECT [FileName]
FROM @filesonly
where [FileName] IS NOT NULL;

Move a File

Here is some T-SQL code that moves the file 3.xml from one folder to another. 

DECLARE @SourceFile AS VARCHAR(500);  
DECLARE @DestinationFile AS VARCHAR(500);  
DECLARE @Cmd AS VARCHAR(500);  
SET @SourceFile = 'c:\junky\3.xml';
SET @DestinationFile = 'c:\junkystuff\3.xml';  
SET @Cmd = 'MOVE ' + @SourceFile + ' ' + @DestinationFile;  
EXEC master.dbo.xp_cmdshell @Cmd;

Return Status (Error Code)

The return code values are 0 (success) or 1 (failure). 

DECLARE @result int = 1;  -- default to fail.  
EXEC @result = xp_cmdshell 'dir C:\MyFolder';  
IF (@result = 0)  
   BEGIN
      PRINT @result
      PRINT 'Success'
   END  
ELSE
   BEGIN  
      PRINT @result
      PRINT 'Failure';
   END 
GO

T-SQL Transactions and bcp

Note that SQL Server transactions do not work with xp_cmdshell using the bcp command. bcp commands inside of a transaction will hang SQL Server.

Spaces in File Name (or directory)

If there are any spaces in the file name or the directory you will need to delimit those with double quotes, as seen in the example below. 

DECLARE @DestinationFile VARCHAR(500) = '';  
declare @sourcefile varchar(500) = '';
DECLARE @Cmd AS VARCHAR(500) = '';  
set @sourcefile = 'c:\Temp\test\my source file.txt'; 
SET @DestinationFile = 'c:\Temp\test\ArchiveTest\my source file.txt.txt'
-- spaces in file name: need to delimit with double quotes as seen below
SET @Cmd = 'MOVE "' + @sourcefile + '" "' + @DestinationFile + '"';  
EXEC master.dbo.xp_cmdshell @Cmd;