sql server - Using bcp utility from within a stored procedure to output records to a text file -

i have been using bcp utility command line save result of query text file e.g.

c:>  bcp "select name [databasename].[dbo].[employees]" queryout "filepath.txt" -c -t  

i trying find away execute , similar bcp commands stored procedure.

research solution

• investigated xp_cmdshell stored procedure (disabled security reasons)
• use sql server agent , use job steps invoke command shell post mssqltips .

attempt @ solution (code below lies within stored procedure)

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 [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 

the outcome of running stored procedure 'execute_bcp' has started.

however, when check file path location specified above the file has not been created.

thanks in advance , advice.