0

I am trying to create a formatted text file generated via stored procedure using xp_cmdshell bcp. It works, but only on C:\ drive. When I try to create the file on E:\ drive or a share drive, I get following error:

SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

I did make sure same folder structure existed on E:\ drive or a share drive, but no luck. This same code works on a different server for E:\ drive as well. I am not technical enough to figure out what could be different between two servers.

My code I am running in Management Studio / Sql Server Agent:

declare @sql varchar(1024);
declare @bcp varchar(1024);
Declare @DateSuffix char(8)
SELECT @DateSuffix = cast(convert(CHAR,GETDATE(),112)as varchar(8)) 
declare @filename varchar(1024)

set @filename = 'C:\Blackline\SB\GL_' + @DateSuffix + '.txt'
set @sql = 'exec VoyagerX.dbo.COPT_uspVOYtoBlackLine_GL_Extract'
set @bcp = 'bcp "' + @sql + '" queryout "' + @filename + '"  -T -c  -S'   

exec master..xp_cmdshell @bcp

If this information helps, I am using service account (I believe which is standard). If this is permission or configuration issue, please let me know!

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53

1 Answers1

0

It turns out, I needed to give full access to service account to a specific folder by right clicking and going through security.

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53