January 10, 2002 at 7:42 am
Hi,
I have an error I am trying to understand. First I am a programmer/developer who has mostly worked with DB2 (20 yrs). I have a database on a server. I was able to use BCP to create a format file for a table. Since the file being loaded has extra fields, I did what the book said and edited the format file and I have included it here. I added fields with a host position of 0. When I am running the bcp command I am on my workstation, I do not have a link to the server other than through Enterprise manager.
I am getting an error "UNABLE TOP OPEN BCP HOST DATA-FILE". I also will try to paste the input(its 400 bytes long). There is no delimiter. Any ideas?
The bcp command
BCP EERSFILL.dbo.US_BANK_ACCOUNT in c:\personal\USBANKBALD.DAT -fUSBANKBALD.FMT -SEERSFILLTEST -T
The format file:
7.0
42
1 SQLCHAR 0 2 "" 0 filler1
2 SQLCHAR 0 4 "" 2 BANK_NUMBER
3 SQLCHAR 0 5 "" 4 COMPANY_NUMBER
4 SQLDATETIME 1 8 "" 3 FILE_DATE
5 SQLCHAR 0 16 "" 5 CORPORATE_ACCOUNT_NUMBER
6 SQLCHAR 0 16 "" 1 CARDHOLD_ACCOUNT_NUMBER
7 SQLCHAR 0 9 "" 6 SOCIAL_SECURITY_NUMBER
8 SQLINT 0 4 "" 7 USER_ACCT_1
9 SQLINT 0 4 "" 8 USER_ACCT_2
10 SQLCHAR 0 2 "" 9 ACCOUNT_STATUS
11 SQLCHAR 0 1 "" 10 BILLING_TYPE
12 SQLCHAR 0 4 "" 11 USER_CODE
13 SQLDATETIME 1 8 "" 12 PAYMENT_DUE_DATE
14 SQLCHAR 0 2 "" 13 CYCLE_DATE
15 SQLCHAR 0 35 "" 14 TBR_HIERARCHY_REPORTING_UNIT
16 SQLDATETIME 1 8 "" 15 ACCOUNT_EXPIRATION_DATE
17 SQLCHAR 0 1 "" 16 CORPORATE_ACCT_FLAG
18 SQLDECIMAL 1 19 "" 21 TOTAL_CURRENT_BAL
19 SQLINT 0 4 "" 17 TOTAL_TRANSACTIONS
20 SQLDECIMAL 1 19 "" 18 TOTAL_DEBITS
21 SQLDECIMAL 1 19 "" 19 TOTAL_CREDITS
22 SQLDECIMAL 1 19 "" 20 TOTAL_PAYMENTS
23 SQLDECIMAL 1 19 "" 22 PREVIOUS_BALANCE
24 SQLDECIMAL 1 19 "" 23 CURRENT_BALANCE
25 SQLDECIMAL 1 19 "" 34 CREDIT_LIMIT
26 SQLDECIMAL 1 19 "" 24 CURRENT_PAYMENT_DUE
27 SQLCHAR 0 10 "" 0 filler2
28 SQLCHAR 0 3 "" 25 NUMBER_OF_CARDS
29 SQLCHAR 0 2 "" 0 FILLER3
30 SQLDECIMAL 1 12 "" 26 AMOUNT_CURRENTLY_PAST_DUE
31 SQLDECIMAL 1 12 "" 27 PAST_DUE_30
32 SQLDECIMAL 1 12 "" 28 PAST_DUE_60
33 SQLDECIMAL 1 12 "" 29 PAST_DUE_90
34 SQLDECIMAL 1 12 "" 30 PAST_DUE_120
35 SQLDECIMAL 1 12 "" 31 PAST_DUE_150
36 SQLDECIMAL 1 12 "" 32 PAST_DUE_180
37 SQLDECIMAL 1 12 "" 33 PAST_DUE_181_PLUS
38 SQLCHAR 0 1 "" 35 ACCT_IN_DISPUTE_FLAG
39 SQLDECIMAL 1 12 "" 36 DISPUTE_AMOUNT
40 SQLDATETIME 1 8 "" 37 LAST_PAYMENT_DATE
41 SQLDECIMAL 1 12 "" 38 LAST_PAYMENT_AMT
42 SQLCHAR 0 3 "/r/n"
The data:
9514253868720020101424604455558111942460400217632734061185790000000001400000000000000000 C +99999991538687000000000000000000000000000000 203Y+00000000.009000001+00000032.00+00000000.00+00000000.00+00000000.00+00000000.00+00000014000.00+00000000000.00 001 +00000000.00+00000000.00+00000000.00+00000000.00+00000000.00+00000000.00+00000000.00+00000000.00N+00000000.0099999999+00000000.
Doesn't look very nice here, hopefully it will when posted. Any help anyone can give will be greatly appreciated. Bill
William H. Hoover
Louisville, Ky
sweeper_bill@yahoo.com
January 10, 2002 at 10:58 am
This sounds like a permissions issue. Can you open this file using notepad?
Steve Jones
January 10, 2002 at 11:04 am
Steve,
I can open the file from notepad, it is on my C: drive of my workstation. I do not know if when running bcp, the ODBC connection looses the security or what. I was told the format file can cause this message, I am not sure. Thanks Bill
William H. Hoover
Louisville, Ky
William H. Hoover
Louisville, Ky
sweeper_bill@yahoo.com
January 10, 2002 at 12:29 pm
January 10, 2002 at 12:37 pm
Hi Steve,
I can not load a single record, it gives me the error right off. I saved the bcp error file (-e option) and it comes up empty. I think you are probably right that it is a permissions thing, I will turn it over to the DBA's and work with them. Thanks for all of your help
William H. Hoover
Louisville, Ky
William H. Hoover
Louisville, Ky
sweeper_bill@yahoo.com
January 10, 2002 at 1:08 pm
January 14, 2002 at 10:29 am
The problem with what I was doing is that the extention on the datafile coming from the ftp command was DAT. The Text file attribute wasn't set. It looks like bcp was looking for a TXT file. We re-ftp'd the file as a TXT and it got rid of the error.
Thanks for all your help. Also, please see my next topic, I am perplexed by what is going on.
William H. Hoover
Louisville, Ky
William H. Hoover
Louisville, Ky
sweeper_bill@yahoo.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply