1) XP_CMDSHELL Directory Search
Let’s start out with a simple directory search example. Say you do not have access to the physical SQL Server machine, but you want to see all the files in the “C:\temp” directory that have a “.sql” extension. You can do this by issuing the following command:
exec master.dbo.xp_cmdshell ‘dir c:\temp\*.sql’
2) Create a new directory using XP_CMDSHELL
Now that wasn’t too exciting, so let’s come up with a more useful example. Imagine you are in the process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, you want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. Assume you do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem you can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:
exec master.dbo.xp_cmdshell ‘mkdir “c:\temp\SQL Agent Output\new_job\”‘
3) Run DTS package via stored procedure using XP_CMDSHELL
As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no “sp_executeDTS” stored procedure, but you can use the following xp_cmdshell command to run your DTS package:
exec master.dbo.xp_cmdshell ‘dtsrun -E -Sserver1 -N”Export Invoices”‘
This command executed the “dtsrun” executable with the necessary parameters to run the “Export Invoices” DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.
4) Execute SQL script via SQLCMD (OSQL) using XP_CMDSHELL
For my last example, suppose you have a T-SQL script stored on a hard drive that you would like to execute via a number of different stored procedures. This T-SQL script does a number of T-SQL commands and you want those same commands to be executed in many different stored procedures. In addition, the T-SQL script is generated by some other organization on a daily basis, so you are unable to include the lines of code in each stored procedure, without changing the stored procedures daily. Therefore, you want to use xp_cmdshell to execute the T-SQL script. To perform this execution you perform the following command in each of your stored procedures:
exec master.dbo.xp_cmdshell ‘osql -E -Sserver1 -i c:\temp\nightly.sql’
Here I have used xp_cmdshell to execute the SQL Server osql utility to process the T-SQL commands contained in the script file “c:\temp\nightly.sql”. See how easy it is to execute a T-SQL batch script via a stored procedure using the xp_cmdshell extended stored procedure!
5) Execute SQL script via XP_CMDSHELL only if the file exists
If you use xp_cmdshell in a script, you need to be aware that if the operating system command fails, your script will not detect that the error occurred. If you want to perform something differently if the operating system command fails then you will need to code your xp_cmdshell execution so you can capture the error. Let me demonstrate what I am talking about.
Say you want to copy an operating system file before you perform some work, but if the operating system file you want to copy does not exist to copy you do not want to perform the additional work. If you execute xp_cmdshell like so:
exec master.dbo.xp_cmdshell ‘copy c:\temp\doesnotexist.txt c:\temp\workfile.txt’Print ‘Copy worked now we can do some more stuff.’…
The “Print” statement above will be executed regardless of whether the file to be copied exists or not. Therefore, if you need to control your logic based on whether or not the “copy” command actually worked, then you should code your xp_command shell execution like this:
DECLARE @rc intEXEC @rc = master.dbo.xp_cmdshell ‘copy c:\temp\doesnotexist.txt c:\temp\workfile.txt’print @rcIF @rc <> 0BEGIN PRINT ‘Copy Failure Skip work’ENDELSEBEGIN Print ‘Copy worked now we can do some more stuff’ ….END
Here I captured the return code of the execution of xp_cmdshell in a variable @rc. This allows me to then check the value held by variable @rc and then I programmatically determine whether or not some additional code should be run or not.
When you use the extended stored procedure xp_cmdshell it runs commands in the background. Because of this, xp_cmdshell “MUST NOT” be used to run programs that require user input. If you try to execute a program that requires user input, the xp_cmdshell process will hang. The process hangs because the program is waiting for user input, but xp_cmdshell will never display the user interface to allow the user to enter data.
If you write lots of T-SQL scripts to automate your Database Administration task then you probably already know about the extended stored procedure xp_cmdshell. This extended stored procedure allows you additional flexibility to get at operation system resources via T-SQL, which is extremely valuable for managing your SQL Server environment. If you have not yet experimented with the power of xp_cmdshell, then take a few minutes to determine how it allows you to enter command level statements via T-SQL.
