SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using xp_cmdshell

By Haidong Ji,

SQL Server exposes command shell in the form of extended stored procedures. There are quite a few of them that are pretty useful, especially if your only way of accessing the server is through SQL Server Enterprise Manager and/or Query Analyzer. For example, xp_fixeddrives can give you free disk information, xp_sendmail allows you to send emails in SQL script. To see how to use them together, see my previous article on monitoring disk space using SQL scripts. Among many extended stored procedures, the most versatile is xp_cmdshell, because it provides you with a DOS interface that you can use to execute many useful commands.

For example, often times we want to find out when the server was last rebooted. This is very easy to do with xp_cmdshell. All you have to do is run xp_cmdshell 'net statistics server'. In the result set, you will see "Statistics since ....". That was when your server was last rebooted.

To use xp_cmdshell effectively, it helps if you know DOS command well. For example, if you use a command like DEL that needs interactive user input, it is going to be hard to automate tasks. To find out all the options or switches that are associated with a command, under DOS, type YourCommand /?, you will see all the options that are available to you. Please note that some command's options are case-sensitive.

When writing these series of articles, I strive to use examples that are easy to follow, touch key aspects of the technology, and provide a template for you to work on. This one is no exception. The following are 2 examples that will help you get started with xp_cmdshell.

Example 1: Stored Procedure to delete files in a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.
CREATE proc usp_DeleteFileInFolder @FolderName varchar(150) as
--Created by Haidong Ji

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
	set @FolderName = @FolderName + '\'
--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation
set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand

Example 2: Stored procedure to automatically generate database schema

Often times, there is a need to keep a backup copy of database schema information, so that an empty database can be easily created without data. You can generate database script using Enterprise Manager. You can also use SQL-DMO for this purpose.(See Andy's intro to DMO). However, SQL Server also provides a nifty utility called scptxfr that you can use to generate database script. This example demonstrate how you can use xp_cmdshell to call an external program to finish some task. This stored procedure will iterate all user databases on the server and generate database scripts for them. The output will be stored in the folder name passed in as an input parameter. Each database will have a separate file. The file name will be in the format of ServerName_DBName_YYYYMMDD.SQL. You can easily schedule this procedure as a job so database scripts will be backup routinely.
CREATE PROCEDURE usp_GenerateDBScripts @FolderName varchar(150) AS
-- Author: Haidong Ji
-- Purpose: This stored procedure will generate DB scripts
-- for each user databases on this server.

-- Note that DB property info will not be generated, such as file location,
-- recovery model, and other db options like Auto Update Statistics,
-- Auto Shrink, etc.

--Note also that you can use UNC path for output file.

-- ***Important*** Depending upon your installation of SQL Server,
-- you might need to change the directory name 
-- to get to the scptxfr.exe utility

set nocount on
declare @DBName varchar(50)
Declare @CMD Varchar(1000)

--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
	set @FolderName = @FolderName + '\'

declare UserDB_Cursor cursor fast_forward for
select name from master.dbo.sysdatabases where name not in ('master', 'tempdb', 'model', 'pubs', 'northwind', 'msdb')

open UserDB_Cursor
fetch next from UserDB_Cursor into @DBName
Select @CMD='Exec master..xp_cmdshell ''"c:\Program Files\Microsoft SQL Server\MSSQL\upgrade\scptxfr.exe" /I /s '
+@@SERVERNAME+' /d ' + @DBName + ' /f ' + @FolderName +@@Servername+'_' + @DBName + '_'+
'.sql /r /O /Y /G '''
print @CMD
Exec (@CMD)
fetch next from UserDB_Cursor into @DBName

close UserDB_Cursor
deallocate UserDB_Cursor


Continuing the discussion of SQL Server admin automation techniques, (see my previous articles on VBScript and WMI), I talk about using command shell to automate various tasks in SQL Server in this article. Hopefully it can provide enough information to get you started.

In addition, Brian Knight's article on xp_cmdshell is also very helpful.

Total article views: 20801 | Views in the last 30 days: 15
Related Articles




Using Xp_cmdshell

One of the most handy extended stored procedures in SQL Server is xp_cmdshell. This article will sh...


xp_cmdshell and DBCC FREEPROCCACHE

SQL Server 2000 SP4 (.2282) / SQL Server Agent / Stored Procedure / xp_cmdshell





XP_cmdshell error.

XP_cmdshell error

sql server 7