It is sometimes necessary to execute external commands in windows command shell from TSQL. The xp_cmdshell extended stored procedure allows you to do just that. Because this command shell can be a potential target for hackers, it is turned off by default. Today, I am going to use this command to solve a real world ETL problem.
The following SQL snippet uses the sp_configure command to enable this option.
-- 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
The xp_cmdshell command takes a valid DOS string as input and executes asynchronously in a windows command shell until the process is finished. The output from the command is rows of variable length text showing the standard output from the execution. A no_output parameter forces the command not to return output. An optional return value can be tested to determine if the command completed successfully.
One common task for an ETL program is to archive files in a sub-directory by date.
It is assumed that the following directory structure and file exists.
The following algorithm is implemented by the TSQL script.
- Get the current date
- Make new directory under \archive by date stamp
- Move data file from \inbound to new sub-directory
-- Local variables DECLARE @VAR_DATE VARCHAR(8); DECLARE @VAR_CMD VARCHAR(512); DECLARE @VAR_RETURN INT; -- 0 - Get the current date SELECT @VAR_DATE = CONVERT(VARCHAR(8), GETDATE(), 112); -- 1 - Compose the new sub-directory command SELECT @VAR_CMD = 'MKDIR C:\ETL\ARCHIVE\' + @VAR_DATE; -- Execute the command EXEC @VAR_RETURN = MASTER..xp_cmdshell @VAR_CMD; -- Did not work IF (@VAR_RETURN > 0) BEGIN RAISERROR ('Unable to create archive sub-directory. Please check the file system for issues.', 16, 1) WITH LOG; END -- 2 - Compose the file move command SELECT @VAR_CMD = 'MOVE C:\ETL\INBOUND\RANK1.CSV C:\ETL\ARCHIVE\' + @VAR_DATE -- Execute the command EXEC @VAR_RETURN = MASTER..xp_cmdshell @VAR_CMD; -- Did not work IF (@VAR_RETURN > 0) BEGIN RAISERROR ('Unable to move the inbound file to the archive sub-directory. Please check the file system for issues.', 16, 1) WITH LOG; END
Like any real world program, testing is required to create a bullet proof program. If this script is executed more than one time a day, it will fail with the following standard output.
output -------------------------------------------------------------- A subdirectory or file C:\ETL\ARCHIVE\20120104 already exists. The system cannot find the file specified.
In summary, the xp_cmdshell is a very handy command for executing ad-hoc commands. It can be used to automate many administrative tasks such as the movement of database files.
The windows process created by xp_cmdshell has the same security context as the SQL Server service account. If you want to reduce the potential of misuse of this command, you can create a proxy account using the sp_xp_cmdshell_proxy_account command. Pick a local account that has fewer privileges than the default service account. I leave this task as a future exercise that you can accomplish.
I will be using this command in the upcoming BCP talk.
June 2013 M T W T F S S « May 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Cloud TagsAFTER ALTER TABLE ALTER TRIGGER CPAN perl modules CREATE DATABASE create function create procedure CREATE TABLE create trigger create view database administrator database developer DATA TYPES DDL DECLARE DELETE DROP DATABASE DROP TRIGGER DROP VIEW execute EXISTS FORMAT free code INSERT ISNULL John F. Miner III PASS perl script REPLACE SELECT sp_help sp_helptext SQL Server SQL Server Management Studio SSMS. string function sys.databases sys.objects sys.schemas TRIGGERS TRUNCATE TABLE TSQL UPDATE USER DEFINED VIEW vb script