SQLServerCentral / SQL Server 7,2000 / T-SQL / BCP error -Unable to open BCP host data-file / Latest Posts

RE: BCP error -Unable to open BCP host data-file

tsandeep1407 — Thu, 21 Mar 2013 11:07:02 GMT

Hi everyone,I have this error output in SSMS for the following queryexec 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 rowsNULLEnter the file storage type of field blobdata [image]: when i run the same query thru cmd prompt, the result is SQLState = S1000, NativeError = 0Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.any of you help me with this please.

RE: BCP error -Unable to open BCP host data-file

ansafmmm — Mon, 07 Jan 2013 00:50:09 GMT

Thank you. It was the wrong directory.

RE: BCP error -Unable to open BCP host data-file

Alban Lijo — Fri, 18 Jun 2010 22:05:13 GMT

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 toldHere are the results and they are Identical-- Through xp_cmdshell Volume in drive C has no label. Volume Serial Number is 402B-0039NULL Directory of c:\NULL06/15/2010 10:54 AM 17,408 1.xls04/14/2010 05:23 PM <DIR> 80613812935150ba7204/14/2010 03:27 PM 0 AUTOEXEC.BAT05/12/2010 04:55 PM 1,652 bar.emf05/07/2010 06:14 PM <DIR> bujji05/19/2010 12:48 PM <DIR> BUJJI_RD04/14/2010 03:27 PM 0 CONFIG.SYS06/14/2010 10:44 AM <DIR> db06/07/2010 02:39 PM <DIR> Documents and Settings02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe06/03/2010 10:27 AM <DIR> Inetpub04/14/2010 05:14 PM 244 Install.log04/14/2010 05:10 PM <DIR> Intel06/17/2010 06:21 PM <DIR> Lijo06/18/2010 10:40 AM 12 lijo.txt04/19/2010 01:25 PM <DIR> MSDE_Database06/02/2010 06:22 PM <DIR> MSVisualStudio6.0006/09/2010 11:48 AM <DIR> oboutSuite06/18/2010 06:12 PM <DIR> Official06/18/2010 04:12 PM <DIR> Program Files04/14/2010 05:12 PM 1,769 RHDSetup.log06/19/2010 09:27 AM 970 service.log05/05/2010 06:18 PM <DIR> Softwares05/25/2010 03:05 PM <DIR> Stanley05/25/2010 03:07 PM 160,178,714 Stanley.zip04/02/2010 02:30 PM 15,944,192 TA.bak06/14/2010 10:38 AM 28,521 test.jpg06/15/2010 10:54 AM 17,408 Test.xls06/15/2010 10:49 AM 8,281 Test.xlsx04/23/2010 11:39 AM <DIR> SAmple06/18/2010 10:31 AM <DIR> WINDOWS06/17/2010 06:14 PM <DIR> WindowsApplication105/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.xls04/14/2010 05:23 PM <DIR> 80613812935150ba7204/14/2010 03:27 PM 0 AUTOEXEC.BAT05/12/2010 04:55 PM 1,652 bar.emf05/07/2010 06:14 PM <DIR> bujji05/19/2010 12:48 PM <DIR> BUJJI_RD04/14/2010 03:27 PM 0 CONFIG.SYS06/14/2010 10:44 AM <DIR> db06/07/2010 02:39 PM <DIR> Documents and Settings02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe06/03/2010 10:27 AM <DIR> Inetpub04/14/2010 05:14 PM 244 Install.log04/14/2010 05:10 PM <DIR> Intel06/17/2010 06:21 PM <DIR> Lijo06/18/2010 10:40 AM 12 lijo.txt04/19/2010 01:25 PM <DIR> MSDE_Database06/02/2010 06:22 PM <DIR> MSVisualStudio6.0006/09/2010 11:48 AM <DIR> oboutSuite06/18/2010 06:12 PM <DIR> Official06/18/2010 04:12 PM <DIR> Program Files04/14/2010 05:12 PM 1,769 RHDSetup.log06/19/2010 09:22 AM 145 service.log05/05/2010 06:18 PM <DIR> Softwares05/25/2010 03:05 PM <DIR> Stanley05/25/2010 03:07 PM 160,178,714 Stanley.zip04/02/2010 02:30 PM 15,944,192 TA.bak06/14/2010 10:38 AM 28,521 test.jpg06/15/2010 10:54 AM 17,408 Test.xls06/15/2010 10:49 AM 8,281 Test.xlsx04/23/2010 11:39 AM <DIR> SAmple06/18/2010 10:31 AM <DIR> WINDOWS06/17/2010 06:14 PM <DIR> WindowsApplication105/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

RE: BCP error -Unable to open BCP host data-file

Sergiy — Fri, 18 Jun 2010 17:06:09 GMT

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:\'

RE: BCP error -Unable to open BCP host data-file

Alban Lijo — Thu, 17 Jun 2010 23:12:38 GMT

Hi,I am trying to execute [b]exec Master..xp_cmdshell 'bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS'[/b]Gives an Output[b]SQLState = S1000, NativeError = 0Error = [Microsoft][SQL Native Client]Unable to open BCP host data-fileNULL[/b][b]The Same Statement when I execute from cmd prompt [/b][b] bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS[/b]Gives the Output Starting copy...[b]4 rows copied.Network packet size (bytes): 4096Clock Time (ms.) Total : 94 Average : (42.55 rows per sec.)[/b]Where am I going wrong...-- Regards Alban Lijo

RE: BCP error -Unable to open BCP host data-file

funooni — Sat, 16 Jan 2010 23:21:53 GMT

Thanks a lot. That's what i was confused with.

RE: BCP error -Unable to open BCP host data-file

Sergiy — Sat, 16 Jan 2010 14:47:38 GMT

It's on [b]server's[/b] C: drive inside temp folder.

RE: BCP error -Unable to open BCP host data-file

funooni — Sat, 16 Jan 2010 05:38:45 GMT

GentlemenI am using BCP in a stored procedure to create an excel XML fileeverything goes fine and i get the following output when the BCP command is executed NULLStarting copy...NULL4 rows copied.Network packet size (bytes): 4096Clock Time (ms.) Total : 1 Average : (4000.00 rows per sec.)NULLbut the problem is i cannot find the file that is created/or should have been createdSET @testString2 = 'BCP "SELECT MAN_Content FROM [DBNAME].[dbo].[TABLENAME] ORDER BY id" queryout "C:\Temp\ECC_MSXLSBySQLServer2005.xls" -C RAW -S SERVERNAME -T -w'Exec Master..xp_cmdshell @testString2 I cant find the file , which i assume should be in my C drive inside temp folder.any help shall be highly appreciated.RegardsZeeshan

RE: BCP error -Unable to open BCP host data-file

Kenneth Wilhelmsson — Mon, 14 May 2007 01:43:00 GMT

Yes, it does. That's what the -S<servername> parameter says...

/Kenneth

RE: BCP error -Unable to open BCP host data-file

fasttrack — Fri, 11 May 2007 07:38:00 GMT

specify the name of server

servername.dbo.T1

otherwise bcp (executed on cmd line) does not where is T1

RE: BCP error -Unable to open BCP host data-file

David A. Long — Fri, 11 May 2007 00:51:00 GMT

This can also be caused by the account BCP is using, since you are using SQL Login, this will be either:

Your account if excuted from Command line

The SQL Server service account if using xp_cmdshell

SQL Server Agent service account, if it is being executed in a CmdExec or T-SQL with xp_cmdshell step. The account can be configured within the Job as well.

The BCP account needs access to the file, for UNC this includes the share.

A trick for Local Service is to grant the SQL Server Computer account these network permissions.

For UNC with the service using a Domain account, the permissions should be verified by logging in as the service account, browse to the file in question, and run BCP from the cmd line.

Andy

RE: BCP error -Unable to open BCP host data-file

rudy - Doctor "X" — Thu, 10 May 2007 07:03:00 GMT

One more point. The table identified as T1 is not qualified. Depending on the user ID supplied this may change what database the table will be looked for in. By default it will be the master database. My suggestion is to qualify the table name on your bcp statement in order to eliminate any potential confusion or errors.

RE: BCP error -Unable to open BCP host data-file

Kenneth Wilhelmsson — Thu, 10 May 2007 02:24:00 GMT

bcp T1 out D:\Archive -S <Server_Name> -U <username> -P <pwd> -b 10000 -c -t |;

.. is this the actual commandline? It looks a bit fishy in two places.Unless you want your file to be named 'Archive', you haven't supplied a filename, only the directory 'D:\Archive'

..is the delimiter a pipe + semicolon?In any case, a pipe on a commandline acts as a redirection operator, so try enlosing it in quotes.bcp T1 out D:\Archive -S <Server_Name> -U <username> -P <pwd> -b 10000 -c -t "|;"(if the semicolon is just some sort of 'statement-end', just remove it-)

/Kenneth

RE: BCP error -Unable to open BCP host data-file

Sankar-403950 — Wed, 09 May 2007 00:24:00 GMT

Hi,

Thanks for quick reply, below is the result

C 9308

D 31946

Pls let me know if you need any other information

Thanks,

Sankar

RE: BCP error -Unable to open BCP host data-file

Sergiy — Tue, 08 May 2007 23:48:00 GMT

Runexec master..xp_fixeddrivesWhat is the result?

RE: BCP error -Unable to open BCP host data-file

Sankar-403950 — Tue, 08 May 2007 23:26:00 GMT

Hi,

I am getting the same problem in the following scenario

I have created one table "T1" using filegroup "A" contains only data file "A_1.ndf" with size 100M and disaled autogrowth. This file group size reaches 100M means its 100% full. Now I am trying to archive this "T1" table data using bcp command as follows:

bcp T1 out D:\Archive -S <Server_Name> -U <username> -P <pwd> -b 10000 -c -t |;

it throws the below error:

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

While executing bcp command, does it requires some space in filegroup "A"?

Can any body help how to resolve this?

--Thanks Sankar

RE: BCP error -Unable to open BCP host data-file

Sergiy — Fri, 27 Apr 2007 17:01:00 GMT

Check permissions for the account running SQL Server Agent.It could be not the same as for SQL Server.

RE: BCP error -Unable to open BCP host data-file

rudy - Doctor "X" — Fri, 27 Apr 2007 09:14:00 GMT

the actual error never made it into the post.

RE: BCP error -Unable to open BCP host data-file

Patrick Nguyen — Fri, 27 Apr 2007 08:42:00 GMT

i got this error while running a bcp command from the xp_cmdshell

if i run the bcp separately on the dos prompt, it runs fine

Anyone knows the info to correct this problem? Thanks

RE: BCP error -Unable to open BCP host data-file

Sapna Ranka — Wed, 16 Nov 2005 13:06:00 GMT

Thanks All for you reply. This issue was resloved. i was trying to BCP out on a logical drive which was mapped correctly. but then my network admin told me that sql server could not access the network drive due to some permission issues.

RE: BCP error -Unable to open BCP host data-file

rudy - Doctor "X" — Wed, 16 Nov 2005 13:00:00 GMT

Also, if there are spaces in the name don't forget the double quotes ... like "C:\Program Files" ... If you're executing from xp_cmdshell you might try a DIR command in xp_cmdshel to check things out security-wise. I'd also stay away from mapped drives since SQL Server is only made aware of them each time the service is started - UNCs are the way to go.

RE: BCP error -Unable to open BCP host data-file

Kenneth Wilhelmsson — Wed, 16 Nov 2005 06:12:00 GMT

Whenever I get this message, it's because of one of three 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) - remember that paths are relative to where bcp is executing. Make sure that bcp.exe can access the file/path from it's context.

/Kenneth

RE: BCP error -Unable to open BCP host data-file

rsharma — Tue, 15 Nov 2005 11:06:00 GMT

You can map the network drive first and then use that mapped drive to do it.

BCP error -Unable to open BCP host data-file

Sapna Ranka — Tue, 15 Nov 2005 09:19:00 GMT

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

NULL

I am getitng the above error when i am executinf BCP. The file location is not on the local machine. it is a network drive. Please help !