Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

BCP error -Unable to open BCP host data-file Expand / Collapse
Author
Message
Posted Friday, June 18, 2010 5:06 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 5:23 PM
Points: 4,576, Visits: 8,341
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:\'
Post #939896
Posted Friday, June 18, 2010 10:05 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:14 AM
Points: 30, 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>
Post #939924
Posted Monday, January 7, 2013 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 12:45 AM
Points: 1, Visits: 14
Thank you. It was the wrong directory.
Post #1403439
Posted Thursday, March 21, 2013 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:56 AM
Points: 7, Visits: 57
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.

Post #1433918
Posted Monday, June 9, 2014 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:27 PM
Points: 2, Visits: 202
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
Post #1578958
Posted Friday, July 4, 2014 8:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 61, Visits: 164
@sergiy

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

Thanks,
Prabhu
Post #1589384
Posted Friday, July 4, 2014 9:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 61, Visits: 164

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
Post #1589395
Posted Friday, July 4, 2014 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1589448
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse