Version : Valid for all SQL server 2012, 2014, 2016
Message: 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.
General Questions related with xp_cmdshell :
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?
Basic Answers: Answer is xp_cmdshell can execute shell/system command, which includes batch file.
Information: xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
SQL server syntax to enable xp_cmdshell:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
Message: 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.
General Questions related with xp_cmdshell :
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?
Basic Answers: Answer is xp_cmdshell can execute shell/system command, which includes batch file.
Information: xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
By default, the xp_cmdshell option is disabled. To enable xp_cmdshell option use the following code.
SQL server syntax to enable xp_cmdshell:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
Example 1: To list all text file in a folder from query editor
exec master.dbo.xp_cmdshell 'dir E:\work\*.txt'
Output:
Volume in drive E has no label.
Volume Serial Number is 3E5F-30C6
NULL
Directory of E:\work
NULL
06/29/2015 11:27 AM 0 8001.txt
1 File(s) 0 bytes
0 Dir(s) 462,489,137,152 bytes free
NULL
Example 2: To create a folder using Sql server query editor
exec master.dbo.xp_cmdshell 'mkdir E:\work\sql_sp_cmdshell\'
Output : NULL
Message: (1 row(s) affected)
Hope you enjoyed lot with given SQL server tricks :) Stay tune