|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
BCP genric errorI 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? Show quote > 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 My interpretation is that the file is empty.> 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? How does the format file look like? What sort file is it? A text file? Binary? -- Erland Sommarskog, SQL Server MVP, esq***@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 (chang***@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 Show quote "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 (chang***@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  |
|||||||||||||||||||||||
Other interesting topics