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 Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2671 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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5011 Visits: 2503
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2671 Visits: 699

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

/Kenneth





funooni
funooni
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 294
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
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: 10990 Visits: 11971
It's on server's C: drive inside temp folder.
funooni
funooni
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 294
Thanks a lot. That's what i was confused with.
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
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