You might have run across several types of issues while using xp_cmdshell to work with dynamic SQL. You may learn more about these issues, their causes, and the recommended fixes in this post.
Error:1
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.
Solution:
The code below uses sp configure to enable xp cmdshell. Each of these settings must be followed by the RECONFIGURE command in order to take effect.
— The below turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure ‘show advanced options’, ‘1’
RECONFIGURE
— The below enables xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, ‘1’
RECONFIGURE
Error:2
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
NULL
- The credentials for the SQL Server service account are required by XP CMDSHELL in order to access the filesystem and other resources. The service account won’t have access to the root of C:. Additionally, write rights must be granted for the folder where the file will be written.
- The identical mistake occurs if you are running a script in your remote SQL Server while your file is in your local machine.
- Check to see that the file is indeed present in the folder you are writing the result-set to.
- Make sure the file-path is properly supplied.
Big Data and SQL 