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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- 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 </span> |
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.
- Get the current date
- Make new directory under \archive by date stamp
- Move data file from \inbound to new sub-directory
|
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 31 32 |
<span style="color: #008000;">-- 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 </span> |
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.
|
1 2 3 4 |
<span style="color: #008000;">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.
Really good put up, I will be viewing back persistently to look for up-grades.
I like this web blog very much, Its a rattling nice position to read and incur info. “Solitude is fine, but you need someone to tell you that solitude is fine.” by Honore’ de Balzac.