BCP genric error

I am trying to BCP locally, first I created the format file using
"BCP...nul..." and then issued the following bcp phrase :
bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
I am getting the genric error
**********************************
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
************************************
The sql server and sqlserver agent are running under a domain account which
is not in the local admin group
I changed this and added him to the local admin group, I get the same error
Thanks for your help
Hi I figured out what is the problem. It was indicating the fullpath for the
format file.
But now I am getting nothing entered in the table, when I issue the bcp I get
*****************
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
********************
So what is missing?

> I am trying to BCP locally, first I created the format file using
> "BCP...nul..." and then issued the following bcp phrase :
> bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
> D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
>
> I am getting the genric error
> **********************************
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
> ************************************
> The sql server and sqlserver agent are running under a domain account which
> is not in the local admin group
> I changed this and added him to the local admin group, I get the same error
> Thanks for your help
|||SalamElias (eliassal@.online.nospam) writes:
> Hi I figured out what is the problem. It was indicating the fullpath for
> the format file. But now I am getting nothing entered in the table, when
> I issue the bcp I get
> *****************
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.) Total : 1
> ********************
> So what is missing?
My interpretation is that the file is empty.
How does the format file look like? What sort file is it? A text file?
Binary?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi,
My understanding of your issue is that:
You used BCP to transfer data, however you found zero rows copied.
If I have misunderstood, please let me know.
I am afraid that the information here is not enough to judge what caused
this issue.
Please first check the database table type definition matches your format
file; then check if there are some special characters in your data file.
I would like to provide you the bcp usage articles for your reference:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
Also, you may mail me (changliw@.microsoft.com) your table definition, the
data file and the format file so that I can reproduce your issue and find a
resolution.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, I have sent you an email
Thanks
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> You used BCP to transfer data, however you found zero rows copied.
> If I have misunderstood, please let me know.
> I am afraid that the information here is not enough to judge what caused
> this issue.
> Please first check the database table type definition matches your format
> file; then check if there are some special characters in your data file.
> I would like to provide you the bcp usage articles for your reference:
> bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> Creating a Format File
> http://msdn2.microsoft.com/en-us/library/ms191516.aspx
> Also, you may mail me (changliw@.microsoft.com) your table definition, the
> data file and the format file so that I can reproduce your issue and find a
> resolution.
> Thanks for using Microsoft Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi,
I got it.
This issue was caused by your format file not matching your data file. I
correct it with the following:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLNUMERIC 0 18 "|" 3 Incoming_Traffic
""
4 SQLNUMERIC 0 18 "\r\n" 4 Outgoing_Traffic
""
Please try it and see if this issue persists at your side.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Salam,
It's my bad. I am so sorry that I omit checking the table. It is indeed a
strange behavior that the last two fields are populated with zero.
I am afraid that I need to consult our SQL team on this and I will let you
know their response as soon as possible.
For now, per my test, you can use SQLCHAR for all the columns:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLCHAR 0 9 "|" 3 Incoming_Traffic
""
4 SQLCHAR 0 9 "\r\n" 4 Outgoing_Traffic
""
The type SQLCHAR will be automatically converted to the database data type
when the data are imported into the table.
I am sorry again for bringing you any inconvenience. Sincerely hope this
suggestion can help resolve your problem.
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
|||Hi Elias,
I am sorry for this late response. I just got the confirmation that the
type SQLNUMERIC is only for SQL native data type.
For a plain text file, all data types in the BCP format file should be
SQLCHAR.
For the explanation, please see Cindy's response as following.
Please feel free to let me know if need further assistance on this issue.
It's always our pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support