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 Wednesday, May 9, 2007 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 10, 2009 6:08 AM
Points: 6, Visits: 6

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

Post #364287
Posted Thursday, May 10, 2007 2:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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




Post #364638
Posted Thursday, May 10, 2007 7:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 3,221, Visits: 2,375
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.



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #364689
Posted Friday, May 11, 2007 12:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:36 PM
Points: 631, Visits: 236

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




Post #365007
Posted Friday, May 11, 2007 7:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 15, 2013 7:37 AM
Points: 351, Visits: 152

specify the name of server

servername.dbo.T1

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




Post #365105
Posted Monday, May 14, 2007 1:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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

/Kenneth




Post #365505
Posted Saturday, January 16, 2010 5:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:04 AM
Points: 205, Visits: 242
Gentlemen

I am using BCP in a stored procedure to create an excel XML file

everything goes fine and i get the following output when the BCP command is executed

NULL
Starting copy...
NULL
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (4000.00 rows per sec.)
NULL

but the problem is i cannot find the file that is created/or should have been created

SET @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.

Regards
Zeeshan
Post #848719
Posted Saturday, January 16, 2010 2:47 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
It's on server's C: drive inside temp folder.
Post #848783
Posted Saturday, January 16, 2010 11:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:04 AM
Points: 205, Visits: 242
Thanks a lot. That's what i was confused with.
Post #848821
Posted Thursday, June 17, 2010 11:12 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
Hi,

I am trying to execute

exec Master..xp_cmdshell 'bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS'

Gives an Output

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL


The Same Statement when I execute from cmd prompt

bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS

Gives the Output

Starting copy...

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 94 Average : (42.55 rows per sec.)

Where am I going wrong...

-- Regards
Alban Lijo


-- Alban Lijo <SQL Rookie>
Post #939354
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse