SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP error -Unable to open BCP host data-file


BCP error -Unable to open BCP host data-file

Author
Message
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
When you run bcp on your local machine C:\ is your local drive.
When you run bcp on the server C:\ is a local drive on he server.

Compare:
"dir C:\" from command prompt and
exec master.dbo.xp_cmdshell 'dir c:\'
Alban Lijo
Alban Lijo
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 64
Thanks Sergiy for the reply..

Sql Server resides in the machine where im executing xp_cmdshell
in that case my local machine and server are the same..

all other commands works fine with xp_cmdshell.. only bcp throws an error ..

So i don't think that is the problem. Any way I have tried Wat u have told

Here are the results and they are Identical

-- Through xp_cmdshell

Volume in drive C has no label.
Volume Serial Number is 402B-0039
NULL
Directory of c:\
NULL
06/15/2010 10:54 AM 17,408 1.xls
04/14/2010 05:23 PM <DIR> 80613812935150ba72
04/14/2010 03:27 PM 0 AUTOEXEC.BAT
05/12/2010 04:55 PM 1,652 bar.emf
05/07/2010 06:14 PM <DIR> bujji
05/19/2010 12:48 PM <DIR> BUJJI_RD
04/14/2010 03:27 PM 0 CONFIG.SYS
06/14/2010 10:44 AM <DIR> db
06/07/2010 02:39 PM <DIR> Documents and Settings
02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe
06/03/2010 10:27 AM <DIR> Inetpub
04/14/2010 05:14 PM 244 Install.log
04/14/2010 05:10 PM <DIR> Intel
06/17/2010 06:21 PM <DIR> Lijo
06/18/2010 10:40 AM 12 lijo.txt
04/19/2010 01:25 PM <DIR> MSDE_Database
06/02/2010 06:22 PM <DIR> MSVisualStudio6.00
06/09/2010 11:48 AM <DIR> oboutSuite
06/18/2010 06:12 PM <DIR> Official
06/18/2010 04:12 PM <DIR> Program Files
04/14/2010 05:12 PM 1,769 RHDSetup.log
06/19/2010 09:27 AM 970 service.log
05/05/2010 06:18 PM <DIR> Softwares
05/25/2010 03:05 PM <DIR> Stanley
05/25/2010 03:07 PM 160,178,714 Stanley.zip
04/02/2010 02:30 PM 15,944,192 TA.bak
06/14/2010 10:38 AM 28,521 test.jpg
06/15/2010 10:54 AM 17,408 Test.xls
06/15/2010 10:49 AM 8,281 Test.xlsx
04/23/2010 11:39 AM <DIR> SAmple
06/18/2010 10:31 AM <DIR> WINDOWS
06/17/2010 06:14 PM <DIR> WindowsApplication1
05/18/2010 01:08 PM <DIR> working folder
14 File(s) 184,526,435 bytes
19 Dir(s) 81,251,590,144 bytes free


-- Through Cmd prompt

Volume in drive C has no label.
Volume Serial Number is 402B-0039

Directory of c:\

06/15/2010 10:54 AM 17,408 1.xls
04/14/2010 05:23 PM <DIR> 80613812935150ba72
04/14/2010 03:27 PM 0 AUTOEXEC.BAT
05/12/2010 04:55 PM 1,652 bar.emf
05/07/2010 06:14 PM <DIR> bujji
05/19/2010 12:48 PM <DIR> BUJJI_RD
04/14/2010 03:27 PM 0 CONFIG.SYS
06/14/2010 10:44 AM <DIR> db
06/07/2010 02:39 PM <DIR> Documents and Settings
02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe
06/03/2010 10:27 AM <DIR> Inetpub
04/14/2010 05:14 PM 244 Install.log
04/14/2010 05:10 PM <DIR> Intel
06/17/2010 06:21 PM <DIR> Lijo
06/18/2010 10:40 AM 12 lijo.txt
04/19/2010 01:25 PM <DIR> MSDE_Database
06/02/2010 06:22 PM <DIR> MSVisualStudio6.00
06/09/2010 11:48 AM <DIR> oboutSuite
06/18/2010 06:12 PM <DIR> Official
06/18/2010 04:12 PM <DIR> Program Files
04/14/2010 05:12 PM 1,769 RHDSetup.log
06/19/2010 09:22 AM 145 service.log
05/05/2010 06:18 PM <DIR> Softwares
05/25/2010 03:05 PM <DIR> Stanley
05/25/2010 03:07 PM 160,178,714 Stanley.zip
04/02/2010 02:30 PM 15,944,192 TA.bak
06/14/2010 10:38 AM 28,521 test.jpg
06/15/2010 10:54 AM 17,408 Test.xls
06/15/2010 10:49 AM 8,281 Test.xlsx
04/23/2010 11:39 AM <DIR> SAmple
06/18/2010 10:31 AM <DIR> WINDOWS
06/17/2010 06:14 PM <DIR> WindowsApplication1
05/18/2010 01:08 PM <DIR> working folder
14 File(s) 184,525,610 bytes
19 Dir(s) 81,251,434,496 bytes free

--Regards
Alban Lijo

-- Alban Lijo <SQL Rookie> :-)
ansafmmm
ansafmmm
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
Thank you. It was the wrong directory.
tsandeep1407
tsandeep1407
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 88
Hi everyone,
I have this error output in SSMS for the following query
exec Master..xp_cmdshell 'bcp "select blobdata from SynDocs.dbo.BLOBDATA where blobid = 2" queryout "c:\filename2.img" -S ROK-W7STHONUK-7 -T'

The output is 2 rows
NULL
Enter the file storage type of field blobdata [image]:

when i run the same query thru cmd prompt, the result is
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.

any of you help me with this please.
ryanbseattle
ryanbseattle
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 280
Kenneth Wilhelmsson (11/16/2005)


Whenever I get this message, it's because of one ofthree things:

1) The path/filename is incorrect (check your typing / spelling)

2) The file does not exist. (make sure the file is where you expect it to be)

3) The file is already open by some other app. (close the other app to release the file)

For 1) and 2) - rememberthat <EM>paths arerelative to where bcp is executing.</EM> Make sure that bcp.exe can access the file/path from it's context.

/Kenneth


This advice helped a lot. I just ran into this BCP problem after a version 8 to version 10 migration.

We are using UNC paths to the local machine (don't ask why), and the file couldn't be accessed. Troubleshooting found that the BCP command needed to be updated to include the -T switch, and the priv's on the \\UNC needed to be reset for the SQL Agent service account.

rbs
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 310
@sergiy

it returns the current drive and its free spaces, could you guide further, I too facing the same issue

Thanks,
Prabhu
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 310

Kenneth Wilhelmsson (11/16/2005)


Whenever I get this message, it's because of one ofthree things:

1) The path/filename is incorrect (check your typing / spelling)

2) The file does not exist. (make sure the file is where you expect it to be)

3) The file is already open by some other app. (close the other app to release the file)

For 1) and 2) - rememberthat <EM>paths arerelative to where bcp is executing.</EM> Make sure that bcp.exe can access the file/path from it's context.

/Kenneth


adding one more point to Kenneth's points

4) Ensure your SQL Server Agent is Enabled / Running, else make it enabled..
Note: Because this point was the issue in my case.

sample Code I executed is as below:

use AdventureWorks2008R2
go

exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'ad hoc distributed queries',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go


--exec master..xp_fixeddrives

declare @sql varchar(8000),@tablename varchar(150)
declare exporttabletotextfile cursor global
for select name from sys.objects where [type]=N'U'

open exporttabletotextfile

fetch exporttabletotextfile into @tablename

while @@FETCH_STATUS=0
begin
select @sql = 'bcp "select * from AdventureWorks2008R2..'+@tablename+'" queryout C:\SSIS\'+@tablename+'.txt -c -t, -T -S ' + @@servername

print @sql

exec xp_cmdshell @sql

fetch next from exporttabletotextfile into @tablename
end

close exporttabletotextfile;
deallocate exporttabletotextfile;

exec sp_configure 'xp_cmdshell',0
go
reconfigure
go
exec master..sp_configure 'ad hoc distributed queries',0
go
reconfigure
go
exec master..sp_configure 'show advanced options',0
go
reconfigure
go



Regards,
Prabhu
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84597 Visits: 41067
tsandeep1407 (3/21/2013)
Hi everyone,
I have this error output in SSMS for the following query
exec Master..xp_cmdshell 'bcp "select blobdata from SynDocs.dbo.BLOBDATA where blobid = 2" queryout "c:\filename2.img" -S ROK-W7STHONUK-7 -T'

The output is 2 rows
NULL
Enter the file storage type of field blobdata [image]:

when i run the same query thru cmd prompt, the result is
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.

any of you help me with this please.



I know it's an older post but the problem here is that the "-c" parameter wasn't specified. I would also recommend the "-C Raw" parameter (notice the difference in case) but leaving that out isn't a showstopper.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ajithrnair
ajithrnair
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 159
The USER might not have permission to the folder mentioned.

-Ajith
sql_only
sql_only
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 61
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?

You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search