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
Sankar-403950
Sankar-403950
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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


Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15607 Visits: 699

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





Rudyx - the Doctor
Rudyx - the Doctor
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23989 Visits: 2529
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."
David A. Long
David A. Long
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4951 Visits: 237

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





fasttrack
fasttrack
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3235 Visits: 152

specify the name of server

servername.dbo.T1

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





Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15607 Visits: 699

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

/Kenneth





funooni
funooni
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 324
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
Sergiy
Sergiy
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59026 Visits: 12969
It's on server's C: drive inside temp folder.
funooni
funooni
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 324
Thanks a lot. That's what i was confused with.
Alban Lijo
Alban Lijo
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 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> :-)
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