Command Shell

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.

• c:\etl\inbound
• c:\etl\archive
• c:\etl\inbound\rank1.csv

The following algorithm is implemented by the TSQL script.

1. Get the current date
2. Make new directory under \archive by date stamp
3. 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.