• xp_cmdshell allows you to run all kind of DOS command.
  • xp_cmdshell allows to test for the existence of files or run batch files...
  • Before using xp_cmdshell, you will need to enable it.
  • Enabling xp_cmdshell in SQL Server is a security risk!
  • Only members of the sysadmin role can execute xp_cmdshell, but you can grant that right to other users.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Enabling xp_cmdshell

  1. EXEC SP_CONFIGURE 'show advanced options', 1
  2. go
  3. RECONFIGURE
  4. go
  5. EXEC SP_CONFIGURE 'xp_cmdshell', 1
  6. go
  7. RECONFIGURE
  8. go
[Get Code]
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Using xp_cmdshell

  1. EXEC master..xp_cmdshell 'dir c:\temp\*.gif'
[Get Code]
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Volume in drive C has no label.
 Volume Serial Number is E403-53AC
NULL
 Directory of c:\temp
NULL
08/20/2007  07:05 PM            36,709 csv-output.gif
07/11/2007  10:38 AM            34,130 Custom_Reports.gif
08/09/2007  02:13 PM            15,384 db-property-files.gif
09/01/2007  05:25 PM            17,533 db-reports.gif
08/22/2007  05:05 PM            12,904 db-restore.gif
09/06/2007  09:53 AM            29,809 dynamic-views.gif
07/11/2007  10:41 AM            14,557 Expensive_Queries01.gif
07/04/2007  04:55 PM            16,816 In_vs_Or.gif
10/08/2007  06:17 PM            28,836 microsoft-performance-byio.gif
10/08/2007  06:15 PM            33,368 microsoft-performance-monitoring.gif
09/01/2007  05:28 PM            36,856 optimize-indexes.gif
09/02/2007  06:21 PM            16,899 rebuild-indexes.gif
09/29/2007  02:45 PM            19,514 running-balance01.gif
09/29/2007  02:37 PM            15,054 running-balance02.gif
08/22/2007  04:39 PM            38,320 single-user.gif
07/31/2007  07:29 AM            33,108 single_user.gif
07/22/2007  04:38 PM            25,488 sqldiag.gif
09/07/2007  04:46 PM            24,303 tablediff-error01.gif
10/01/2007  06:22 PM            13,291 views-vs-tables-01.gif
10/01/2007  06:25 PM            20,628 views-vs-tables-02.gif
06/26/2007  10:05 AM             9,389 WherePlain.gif
06/26/2007  10:04 AM            13,571 WhereUCase.gif
              22 File(s)        506,467 bytes
               0 Dir(s)   1,766,424,576 bytes free
NULL

(30 row(s) affected)

Output is an nvarchar(255) column.

Using xp_cmdshell to test for existing files

xp_cmdshell returns either 0 which is success or 1 which is failure [it's the reverse of the boolean logic, it's the same as the DOS logic].

  1. DECLARE @worked INT
  2. EXEC @worked = master..xp_cmdshell 'dir c:\temp\*.gif'
  3. IF (@worked = 0)
  4.    PRINT '1. Yes - GIFs presents'
  5. ELSE
  6.    PRINT '1. No GIF'
  7. go
[Get Code]
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Volume in drive C has no label.
 Volume Serial Number is E403-53AC
NULL
 Directory of c:\temp
NULL
08/20/2007  07:05 PM            36,709 csv-output.gif
07/11/2007  10:38 AM            34,130 Custom_Reports.gif
08/09/2007  02:13 PM            15,384 db-property-files.gif
09/01/2007  05:25 PM            17,533 db-reports.gif
08/22/2007  05:05 PM            12,904 db-restore.gif
09/06/2007  09:53 AM            29,809 dynamic-views.gif
07/11/2007  10:41 AM            14,557 Expensive_Queries01.gif
07/04/2007  04:55 PM            16,816 In_vs_Or.gif
10/08/2007  06:17 PM            28,836 microsoft-performance-byio.gif
10/08/2007  06:15 PM            33,368 microsoft-performance-monitoring.gif
09/01/2007  05:28 PM            36,856 optimize-indexes.gif
09/02/2007  06:21 PM            16,899 rebuild-indexes.gif
09/29/2007  02:45 PM            19,514 running-balance01.gif
09/29/2007  02:37 PM            15,054 running-balance02.gif
08/22/2007  04:39 PM            38,320 single-user.gif
07/31/2007  07:29 AM            33,108 single_user.gif
07/22/2007  04:38 PM            25,488 sqldiag.gif
09/07/2007  04:46 PM            24,303 tablediff-error01.gif
10/01/2007  06:22 PM            13,291 views-vs-tables-01.gif
10/01/2007  06:25 PM            20,628 views-vs-tables-02.gif
06/26/2007  10:05 AM             9,389 WherePlain.gif
06/26/2007  10:04 AM            13,571 WhereUCase.gif
              22 File(s)        506,467 bytes
               0 Dir(s)   1,766,293,504 bytes free
NULL

(30 row(s) affected)

1. Yes - GIFs presents
  • You can do the same xp_cmdshell [Line 2], but without the output by adding ,no_output.
  1. DECLARE @worked INT
  2. EXEC @worked = master..xp_cmdshell 'dir c:\temp\*.gif', no_output
  3. IF (@worked = 0)
  4.    PRINT '2. Yes - GIFs presents'
  5. ELSE
  6.    PRINT '2. No GIF'
  7. go
[Get Code]
2. Yes - GIFs presents