January 8, 2016 at 6:43 am
sql_only (1/8/2016)
I want to import data to my database via bcp like thisbcp 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
Change is inevitable... Change for the better is not.
January 8, 2016 at 6:50 am
Jeff Moden (1/8/2016)
sql_only (1/8/2016)
I want to import data to my database via bcp like thisbcp 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
January 8, 2016 at 7:05 am
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 thisbcp 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
Change is inevitable... Change for the better is not.
January 8, 2016 at 7:18 am
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 thisbcp 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
January 8, 2016 at 7:25 am
They would be correct on that quote problem. Apologies for missing that.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2016 at 1:32 am
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
March 17, 2017 at 2:26 am
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
May 31, 2017 at 12:05 am
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
July 27, 2017 at 8:42 am
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.
July 27, 2017 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2017 at 2:44 pm
Agreed, I ran it to make sure it was working and then blew it away and redid it on a fileserver
June 11, 2018 at 6:40 am
Jeff Moden - Thursday, July 27, 2017 1:54 PMBCP 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
June 11, 2018 at 5:17 pm
Rick-153145 - Monday, June 11, 2018 6:40 AMJeff Moden - Thursday, July 27, 2017 1:54 PMBCP 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
June 12, 2018 at 1:26 am
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. 🙂
February 5, 2020 at 2:18 pm
in my event is was just, that the profile had to be declared "public" in SSMS / Management / Database Mail.
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply