I have a storeprocedure which I have created as a sql job and the job writes into a text file.
The code that does it in the SP are as follows:
declare @cmdLineString varchar(1000)
SET @cmdLineString = 'sqlcmd -d Fwreports -q "select * from facilities" -U sa -P Dr0fnarc -o "C:\output.txt"'
EXEC master..xp_cmdshell @cmdLineString
But when the job is run it gives the following error:
The job failed. The Job was invoked by User sa. The last step to run was step 1 (Virtua EMR PerDiem).,00:00:00,0,0,,,,
06/20/2013 12:52:23,Virtua EMR PerDiem,Error,1,NJ0DB1\XXX
FW,Virtua EMR PerDiem,Virtua EMR PerDiem,,Executed as user: NT AUTHORITY\SYSTEM. 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.
Please help me resolve this.is there any command that I need to call before I run the xpcmd shell command to write to the file.I do not want to configure on the DB level because of security reasons.Can I implement in the SP itself.