BCP error -Unable to open BCP host data-file

• Jeff Moden

  SSC Guru

  Points: 1003861

  More actions

  January 8, 2016 at 6:43 am

  #1850494

  sql_only (1/8/2016)

  ---

  I want to import data to my database via bcp like this

  bcp mydb.dbo.mytable in 'C:\_value_.txt' -S mypc\instancename -T

  But the error I keep getting is unable to open BCP host data -file

  Why?

  Where does 'C:\_value_.txt' live? On the SQL Server or somewhere else?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sql_only

  Old Hand

  Points: 331

  More actions

  January 8, 2016 at 6:50 am

  #1850500

  Jeff Moden (1/8/2016)

  ---

  sql_only (1/8/2016)

  ---

  I want to import data to my database via bcp like this

  bcp mydb.dbo.mytable in 'C:\_value_.txt' -S mypc\instancename -T

  But the error I keep getting is unable to open BCP host data -file

  Why?

  Where does 'C:\_value_.txt' live? On the SQL Server or somewhere else?

  It's on my main hard drive of the computer that is also hosting sql server

  You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

• Jeff Moden

  SSC Guru

  Points: 1003861

  More actions

  January 8, 2016 at 7:05 am

  #1850508

  sql_only (1/8/2016)

  ---

  Jeff Moden (1/8/2016)

  ---

  sql_only (1/8/2016)

  ---

  I want to import data to my database via bcp like this

  bcp mydb.dbo.mytable in 'C:\_value_.txt' -S mypc\instancename -T

  But the error I keep getting is unable to open BCP host data -file

  Why?

  Where does 'C:\_value_.txt' live? On the SQL Server or somewhere else?

  It's on my main hard drive of the computer that is also hosting sql server

  I guess my first suggestion would be to never store stuff in the root directory of any drive.

  Also, do you have the file open somewhere else? Unlike many other programs, SQL Server/BCP won't open the file unless something like NotePad (which copies the file into memory and releases it) has it open.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sql_only

  Old Hand

  Points: 331

  More actions

  January 8, 2016 at 7:18 am

  #1850516

  Jeff Moden (1/8/2016)

  ---

  sql_only (1/8/2016)

  ---

  Jeff Moden (1/8/2016)

  ---

  sql_only (1/8/2016)

  ---

  I want to import data to my database via bcp like this

  bcp mydb.dbo.mytable in 'C:\_value_.txt' -S mypc\instancename -T

  But the error I keep getting is unable to open BCP host data -file

  Why?

  Where does 'C:\_value_.txt' live? On the SQL Server or somewhere else?

  It's on my main hard drive of the computer that is also hosting sql server

  I guess my first suggestion would be to never store stuff in the root directory of any drive.

  Also, do you have the file open somewhere else? Unlike many other programs, SQL Server/BCP won't open the file unless something like NotePad (which copies the file into memory and releases it) has it open.

  Hi

  Thanks for the reply.

  This file is actually there for testing purposes it is not always there.

  I was able to solve this by removing the single quotes. Was told they should either be double quotes or they should not be any quotes

  Thx

  You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

• Jeff Moden

  SSC Guru

  Points: 1003861

  More actions

  January 8, 2016 at 7:25 am

  #1850519

  They would be correct on that quote problem. Apologies for missing that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• reddysekharreddy6

  SSC Journeyman

  Points: 83

  More actions

  July 11, 2016 at 1:32 am

  #1889344

  Microsoft SQL Server bcp is executed in C drive, so the path specified to copy is also in the C drive only.

  For example:-

  Not Working:

  --------------

  DECLARE @bcp_cmd4 VARCHAR(1000);

  DECLARE @exe_path4 VARCHAR(200) =

  ' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ';

  SET @bcp_cmd4 = @exe_path4 +

  ' BCP.EXE "SELECT AccountNumber FROM SEPDB_ONLINE.dbo.SEP_RetailStore" queryout ' +

  ' "D:\Reddy.txt" -T -c -q -t0x7c -r';

  PRINT @bcp_cmd4;

  EXEC master..xp_cmdshell @bcp_cmd4;

  GO

  Working:

  ---------

  DECLARE @bcp_cmd4 VARCHAR(1000);

  DECLARE @exe_path4 VARCHAR(200) =

  ' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ';

  SET @bcp_cmd4 = @exe_path4 +

  ' BCP.EXE "SELECT AccountNumber FROM SEPDB_ONLINE.dbo.SEP_RetailStore" queryout ' +

  ' "C:\Reddy.txt" -T -c -q -t0x7c -r';

  PRINT @bcp_cmd4;

  EXEC master..xp_cmdshell @bcp_cmd4;

  GO

• chris.stuart

  SSCarpal Tunnel

  Points: 4965

  More actions

  March 17, 2017 at 2:26 am

  #1933700

  Old post but here is what I ran into and this is the answer to all the problems. I tried everything in this post and thought let me give one of my other solutions a try
  Althought SQL Server can see the local D drive it somehow doesnt like the sub directories, I mapped a drive to the local drive and then do the export:

  declare @CmdShell varchar(1000)
EXEC XP_CMDSHELL 'net use X: /delete'

  set @CmdShell = 'net use X: "' + '\\Jhb-xx\ASD BI\Reports and Requests' + '" /user:xxxx\xxx xxx'
EXEC XP_CMDSHELL @CmdShell

  -- Query 1 clients

  IF (SELECT OBJECT_ID('tempdb..##Query1')) IS NOT NULL
drop table ##Query1

  SELECT
.....
into ##Query1
FROM xxx
order by 1

  declare @SQL varchar(1000)

  select @SQL = 'bcp "select * from ##Query1 order by 1'
      + '" queryout "' + 'X:\A1.Txt" -c -t"~" -T'

  exec master..xp_cmdshell @SQL

  Works 100% but trying to export to the "D:\ASD BI\Reports and Requests" drive, directory (although local) causes the problem

• umaShankar godi

  SSC Enthusiast

  Points: 183

  More actions

  May 31, 2017 at 12:05 am

  #1944671

  Recently I have faced the similar issue "unable to open bcp host data-file" and it took my whole day, Workaround followed is , In the out/queryout option do not give the file path just give the filename, it creates the output file in same directory of the where bcp command is exexcuted. then move the file to required location using in bat file.

  
  UmaShankar

• Matthew Cushing

  SSCrazy

  Points: 2898

  More actions

  July 27, 2017 at 8:42 am

  #1953043

  Not sure if I'm the only dork who forgot that if you use C or D drive as your download location, it's the physical drive that the server its located on.  Took me a bit to realize I made it on my local machine and not the server.  Created the drive on the server and BAM.  Definite facepalm moment.

• Jeff Moden

  SSC Guru

  Points: 1003861

  More actions

  July 27, 2017 at 1:54 pm

  #1953104

  BCP works fine with UNC's, as well.  Don't use your server as a download repository even for staging a table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Matthew Cushing

  SSCrazy

  Points: 2898

  More actions

  July 27, 2017 at 2:44 pm

  #1953111

  Agreed, I ran it to make sure it was working and then blew it away and redid it on a fileserver

• Rick-153145

  SSCrazy

  Points: 2823

  More actions

  June 11, 2018 at 6:40 am

  #1993533

  Jeff Moden - Thursday, July 27, 2017 1:54 PM

  BCP works fine with UNC's, as well.  Don't use your server as a download repository even for staging a table.

  Sorry to bring up an old thread, but I came across the same issue and it seems it is to do with UAC:
  Set share to have everyone with full control and all sub directories checked etc:

  This doesn't work:
  

  declare @sFilename varchar(80)

  declare @sRepository varchar(800)

  SET @sRepository = '\\a network share'

  Set @sFilename = 'Test.csv'

  DECLARE @ReportName varchar(1000)

  DECLARE @SQL2 varchar(4000)

  DECLARE @bcpCommand varchar(8000)

  DECLARE @tblBCPresults TABLE (ErrMsg VARCHAR(500));

  DECLARE @iError int

  SET @ReportName = @sRepository + '\'+ @sFilename

  SET @SQL2 = 'SELECT 1 ;'

  SET @bcpCommand = 'bcp "'+@SQL2+'" queryout "'

  SET @bcpCommand = @bcpCommand + @ReportName + '" -T -c -C RAW -S ' + @@SERVERNAME

  select @bcpCommand

  EXEC @iError = master..xp_cmdshell @bcpCommand

  This does work:
  

  declare @sFilename varchar(80)

  declare @sRepository varchar(800)

  declare @CmdShell varchar(1000)

  EXEC XP_CMDSHELL 'net use X: /delete'

  set @CmdShell = 'net use X: "' + '\\a network share' + '" /user:Domain\user xxxxx'

  EXEC XP_CMDSHELL @CmdShell

  SET @sRepository = 'X:\'

  Set @sFilename = 'test.csv'

  DECLARE @ReportName varchar(1000)

  DECLARE @SQL2 varchar(4000)

  DECLARE @bcpCommand varchar(8000)

  DECLARE @tblBCPresults TABLE (ErrMsg VARCHAR(500));

  DECLARE @iError int

  SET @ReportName = @sRepository + '\'+ @sFilename

  SET @SQL2 = 'SELECT 1 ;'

  SET @bcpCommand = 'bcp "'+@SQL2+'" queryout "'

  SET @bcpCommand = @bcpCommand + @ReportName + '" -T -c -C RAW -S ' + @@SERVERNAME

  --select @bcpCommand

  EXEC @iError = master..xp_cmdshell @bcpCommand

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  June 11, 2018 at 5:17 pm

  #1993619

  Rick-153145 - Monday, June 11, 2018 6:40 AM

  Jeff Moden - Thursday, July 27, 2017 1:54 PM

  BCP works fine with UNC's, as well.  Don't use your server as a download repository even for staging a table.

  Sorry to bring up an old thread, but I came across the same issue and it seems it is to do with UAC:
  Set share to have everyone with full control and all sub directories checked etc:

  This doesn't work:
  

  declare @sFilename varchar(80)

  declare @sRepository varchar(800)

  SET @sRepository = '\\a network share'

  Set @sFilename = 'Test.csv'

  DECLARE @ReportName varchar(1000)

  DECLARE @SQL2 varchar(4000)

  DECLARE @bcpCommand varchar(8000)

  DECLARE @tblBCPresults TABLE (ErrMsg VARCHAR(500));

  DECLARE @iError int

  SET @ReportName = @sRepository + '\'+ @sFilename

  SET @SQL2 = 'SELECT 1 ;'

  SET @bcpCommand = 'bcp "'+@SQL2+'" queryout "'

  SET @bcpCommand = @bcpCommand + @ReportName + '" -T -c -C RAW -S ' + @@SERVERNAME

  select @bcpCommand

  EXEC @iError = master..xp_cmdshell @bcpCommand

  This does work:
  

  declare @sFilename varchar(80)

  declare @sRepository varchar(800)

  declare @CmdShell varchar(1000)

  EXEC XP_CMDSHELL 'net use X: /delete'

  set @CmdShell = 'net use X: "' + '\\a network share' + '" /user:Domain\user xxxxx'

  EXEC XP_CMDSHELL @CmdShell

  SET @sRepository = 'X:\'

  Set @sFilename = 'test.csv'

  DECLARE @ReportName varchar(1000)

  DECLARE @SQL2 varchar(4000)

  DECLARE @bcpCommand varchar(8000)

  DECLARE @tblBCPresults TABLE (ErrMsg VARCHAR(500));

  DECLARE @iError int

  SET @ReportName = @sRepository + '\'+ @sFilename

  SET @SQL2 = 'SELECT 1 ;'

  SET @bcpCommand = 'bcp "'+@SQL2+'" queryout "'

  SET @bcpCommand = @bcpCommand + @ReportName + '" -T -c -C RAW -S ' + @@SERVERNAME

  --select @bcpCommand

  EXEC @iError = master..xp_cmdshell @bcpCommand

  Giving everyone full control might not be enough.
  You need specifically allow read/write access to that folder for the proxy account which executes bcp from xp_cmdshell environment.

  _____________
  Code for TallyGenerator

• Rick-153145

  SSCrazy

  Points: 2823

  More actions

  June 12, 2018 at 1:26 am

  #1993634

  Thanks sergiy, after I posted this, I deleted and re-added the shares (added by someone else and at each level of the folder structure) and it fixed the issue. Very strange, but at least my post gave me an idea on what the issue was. 🙂

• herbert.tobisch 95615

  SSC Rookie

  Points: 39

  More actions

  February 5, 2020 at 2:18 pm

  #3723302

  in my event is was just, that the profile had to be declared "public" in SSMS / Management / Database Mail.