I was doing some work with SQL Server today and came across a solution I’ve used a lot over the years. This is a perfect candidate for my “Aides-Memoires” category.
Part of the task involved copy a file from one location to another using T-SQL. This is simple to achieve:
DECLARE @CommandText varchar(1000) SET @CommandText = 'copy [source] [destination]' EXEC master..xp_cmdshell @CommandText
However, when I executed the code above I got the following error:
“SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.”
The solution to this is simple. Run the following command to enable the use of xp_cmdshell:
-- 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
After running the T-SQL above, the code to copy the file worked as expected.