Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
SET NOCOUNT ON

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
GO

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
WHILE @@FETCH_STATUS = 0
Begin
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 + '_'+
Replace(convert(Varchar(8),getdate(),112)+'_'+convert(Varchar(8),getdate(),114),':','')+
'.sql /r /O /Y /G '''
print @CMD
Exec (@CMD)
fetch next from UserDB_Cursor into @DBName
end

close UserDB_Cursor
deallocate UserDB_Cursor
GO

Conclusion

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: 20698 | Views in the last 30 days: 23
 
Related Articles
FORUM

xp_cmdshell

xp_cmdshell

ARTICLE

Using Xp_cmdshell

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

FORUM

xp_cmdshell and DBCC FREEPROCCACHE

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

FORUM

Xp_cmdshell

Xp_cmdshell

FORUM

XP_cmdshell error.

XP_cmdshell error

Tags
administration    
miscellaneous    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones