{"id":1277,"date":"2012-01-05T02:45:27","date_gmt":"2012-01-05T02:45:27","guid":{"rendered":"http:\/\/craftydba.com\/?p=1277"},"modified":"2012-01-05T03:00:39","modified_gmt":"2012-01-05T03:00:39","slug":"command-shell","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1277","title":{"rendered":"Command Shell"},"content":{"rendered":"<p>It is sometimes necessary to execute external commands in windows command shell from TSQL. The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms175046(SQL.100).aspx\">xp_cmdshell<\/a> 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.<\/p>\n<p>The following SQL snippet uses the sp_configure command to enable this option.<\/p>\n<pre><span style=\"color: #008000;\">-- To allow advanced options to be changed.\r\nEXEC sp_configure 'show advanced options', 1\r\nGO\r\n\r\n-- To update the currently configured value for advanced options.\r\nRECONFIGURE\r\nGO\r\n\r\n-- To enable the feature.\r\nEXEC sp_configure 'xp_cmdshell', 1\r\nGO\r\n\r\n-- To update the currently configured value for this feature.\r\nRECONFIGURE\r\nGO\r\n<\/span><\/pre>\n<\/p>\n<p>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.<\/p>\n<p>One common task for an ETL program is to archive files in a sub-directory by date.<\/p>\n<p>It is assumed that the following directory structure and file exists.<\/p>\n<ul>\n<li>c:\\etl\\inbound<\/li>\n<li>c:\\etl\\archive<\/li>\n<li>c:\\etl\\inbound\\rank1.csv<\/li>\n<\/ul>\n<p>The following algorithm is implemented by the TSQL script.<\/p>\n<ol>\n<li>Get the current date<\/li>\n<li>Make new directory under \\archive by date stamp<\/li>\n<li>Move data file from \\inbound to new sub-directory<\/li>\n<\/ol>\n<pre><span style=\"color: #008000;\">-- Local variables\r\nDECLARE @VAR_DATE VARCHAR(8);\r\nDECLARE @VAR_CMD VARCHAR(512);\r\nDECLARE @VAR_RETURN INT;\r\n\r\n-- 0 - Get the current date \r\nSELECT @VAR_DATE = CONVERT(VARCHAR(8), GETDATE(), 112);\r\n\r\n-- 1 - Compose the new sub-directory command\r\nSELECT @VAR_CMD = 'MKDIR C:\\ETL\\ARCHIVE\\' + @VAR_DATE;\r\n\r\n-- Execute the command\r\nEXEC @VAR_RETURN = MASTER..xp_cmdshell @VAR_CMD;\r\n\r\n-- Did not work\r\nIF (@VAR_RETURN &gt; 0)\r\nBEGIN\r\n    RAISERROR ('Unable to create archive sub-directory.  Please check the file system for issues.', 16, 1) WITH LOG;\r\nEND\r\n\r\n-- 2 - Compose the file move command\r\nSELECT @VAR_CMD = 'MOVE C:\\ETL\\INBOUND\\RANK1.CSV C:\\ETL\\ARCHIVE\\' + @VAR_DATE\r\n\r\n-- Execute the command\r\nEXEC @VAR_RETURN = MASTER..xp_cmdshell @VAR_CMD;\r\n\r\n-- Did not work\r\nIF (@VAR_RETURN &gt; 0)\r\nBEGIN\r\n    RAISERROR ('Unable to move the inbound file to the archive sub-directory.  Please check the file system for issues.', 16, 1) WITH LOG;\r\nEND\r\n<\/span><\/pre>\n<\/p>\n<p>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.<\/p>\n<pre><span style=\"color: #008000;\">output\r\n--------------------------------------------------------------\r\nA subdirectory or file C:\\ETL\\ARCHIVE\\20120104 already exists.\r\nThe system cannot find the file specified.\r\n<\/pre>\n<p><\/span> <\/p>\n<p>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.<\/p>\n<p>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 <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190359(SQL.100).aspx\">sp_xp_cmdshell_proxy_account<\/a> 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.<\/p>\n<p>I will be using this command in the upcoming BCP talk.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. &#8212; To allow advanced options to be changed. EXEC sp_configure &#8216;show advanced options&#8217;, 1 GO &#8212; To update the currently configured&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,12,15,264,28,29,263],"class_list":["post-1277","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-free-code","tag-john-f-miner-iii","tag-sp_xp_cmdshell_proxy_account","tag-sql-server","tag-tsql","tag-xp_cmdshell"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1277","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1277"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1277\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}