title: Using bcp utility from within a stored procedure to output records to a text file
body: <p>I have been using the bcp utility from the command line to save the result of a query to a text file e.g. </p>
  
  <pre><code>C:&gt;  bcp "SELECT Name FROM [DatabaseName].[dbo].[Employees]" queryout "filepath.txt" -c -T 
  </code></pre>
  
  <p>I am currently trying to find away to execute this and similar bcp commands from a stored procedure. </p>
  
  <p>Research for solution </p>
  
  <ul>
  <li>Investigated xp_cmdshell stored procedure (Disabled for security reasons)</li>
  <li>Use SQL Server agent and use job steps to invoke the command shell
  <a href="http://www.mssqltips.com/sqlservertip/2014/replace-xpcmdshell-command-line-use-with-sql-server-agent/" rel="nofollow">Post from mssqltips</a>
  .</li>
  </ul>
  
  <p>Attempt at a solution (Code below lies within stored procedure)</p>
  
  <pre><code>DECLARE @job NVARCHAR(100)
  SET @job ='execute_bcp' 
  
  EXEC msdb..sp_add_job @job_name = @job, 
    @description = 'Execute bcp command', 
    @owner_login_name = 'sa',
    @delete_level = 1
  
  EXEC msdb..sp_add_jobstep @job_name = @job,
    @step_id = 1,
    @step_name ='Command Shell Execution', @subsystem = 'CMDEXEC',
    @command = 'bcp "SELECT Name FROM [DatabaseName].[dbo].[Employees]" queryout "filepath.txt" -c -T',
    @on_success_action =1
  
  EXEC msdb..sp_add_jobserver @job_name =@job
  
  EXEC msdb..sp_start_job @job_name = @job
  </code></pre>
  
  <p>The outcome of running this stored procedure is 
  'execute_bcp' has started. </p>
  
  <p>However, when I check the file path location specified above <strong>the file has not been created.</strong> </p>
  
  <p>Thanks in advance for any help and advice. </p>