Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

BCP import problems

1,330 views
Skip to first unread message

Robert

unread,
Jun 19, 2003, 5:44:08 AM6/19/03
to
I have a BCP error creating a BCP job with the error message as
"SQLState = S1002, NativeError = 0 Error = [Microsoft][ODBC SQL
Server Driver]Invalid Descriptor Index. Process Exit Code 1. The
step failed."

I have tried many ways to configure the code and still no joy.
The field names and sizes are different in the format file as they are
in the table would that cause the problem?
The the full information is below.

Table configuration is as follows:

CREATE TABLE [dbo].[tbl_billingdetails] (
[tele_no] [char] (11) NOT NULL ,
[qtr_inv_id] [smallint] NOT NULL ,
[total_bill] [decimal](12, 0) NULL ,
[local_call] [decimal](12, 0) NULL ,
[nat_call] [decimal](12, 0) NULL ,
[idd_call] [decimal](12, 0) NULL ,
[other_call] [decimal](12, 0) NULL ,
[bill_date] [smalldatetime] NULL ,
[div_chrg] [decimal](12, 0) NULL ,
[adv_chrg] [decimal](12, 0) NULL ,
[oth_chrg] [decimal](12, 0) NULL ,
[misc_call] [decimal](12, 0) NULL ,
[tele_call] [decimal](12, 0) NULL ,
[ring_call] [decimal](12, 0) NULL ,
[rem_call] [decimal](12, 0) NULL ,
[fwd_call] [decimal](12, 0) NULL ,
[chrg_call] [decimal](12, 0) NULL ,
[oper_call] [decimal](12, 0) NULL ,
[chrg_adv] [decimal](12, 0) NULL ,
[call_allow] [decimal](12, 0) NULL ,
[tw_call] [decimal](12, 0) NULL ,
[chrg_amt] [decimal](12, 0) NULL ,
[call_disc] [decimal](12, 0) NULL ,
[mobile_call] [decimal](12, 0) NULL ,
[imported] [smalldatetime] NULL
)

6.0
29
1 SQLCHAR 0 9 "" 0 blank1
2 SQLCHAR 0 15 "" 2 tele_no
3 SQLCHAR 0 4 "" 0 blank2
4 SQLCHAR 0 6 "" 4 qtr_inv_id
5 SQLCHAR 0 7 "" 0 blank3
6 SQLCHAR 0 14 "" 6 total_bill
7 SQLCHAR 0 14 "" 7 local_call
8 SQLCHAR 0 14 "" 8 nat_call
9 SQLCHAR 0 14 "" 9 idd_call
10 SQLCHAR 0 14 "" 10 other_call
11 SQLCHAR 0 8 "" 11 bill_date
12 SQLCHAR 0 4 "" 0 blank4
13 SQLCHAR 0 14 "" 0 blank5
14 SQLCHAR 0 14 "" 14 div_chrg
15 SQLCHAR 0 14 "" 15 adv_chrg
16 SQLCHAR 0 14 "" 16 oth_chrg
17 SQLCHAR 0 14 "" 17 misc_call
18 SQLCHAR 0 14 "" 18 tele_call
19 SQLCHAR 0 14 "" 19 ring_call
20 SQLCHAR 0 14 "" 20 rem_call
21 SQLCHAR 0 14 "" 21 fwd_call
22 SQLCHAR 0 14 "" 22 chrg_call
23 SQLCHAR 0 14 "" 23 oper_call
24 SQLCHAR 0 14 "" 24 chrg_adv
25 SQLCHAR 0 14 "" 25 call_allow
26 SQLCHAR 0 14 "" 26 tw_call
27 SQLCHAR 0 14 "" 27 chrg_amt
28 SQLCHAR 0 14 "" 28 call_disc
29 SQLCHAR 0 14 "\r\n" 29 mobile_call


Text file data is (ignore the speach marks, used to show the full line
of data):

"70978561 02016120011 1 017 40010 000000000000 000000000000
000000000000 000000000000 000000000000 20030217 1
000000000000 000000000000 000000000000 000000000000 000000000000
000000000000 000000000000 000000000000 000000000000 000000000000
000000000000 000000000000 000000000000 000000000000 000000000000
000000000000 "

the BCP code:

bcp dbname..tmp_billingdetails in f:\inetpub\wwwroot\dbname\bills1.txt
-f f:\inetpub\wwwroot\out\billing.fmt -e
f:\inetpub\wwwroot\dbname\err.fil -U joe1 -P joepass -S

Please can anyone help I would be very thankful if you could.
Regards
Robert.

Erland Sommarskog

unread,
Jun 19, 2003, 6:15:51 PM6/19/03
to
[posted and mailed, please reply in news]

Robert (r.ma...@ntlworld.com) writes:
> I have a BCP error creating a BCP job with the error message as
> "SQLState = S1002, NativeError = 0 Error = [Microsoft][ODBC SQL
> Server Driver]Invalid Descriptor Index. Process Exit Code 1. The
> step failed."

That was not a very informative message. A better message would be
"Error in format file. Excessive server colunms specified." Because
that is the error. In the next-to-right most column you either mirror
the number in the leftmost column, or use a 0 for data you ignore.
But the number should be the column number in the server. The column
names are informational only, and BCP to not use them.

Here is a corrected format file:

7.0


29
1 SQLCHAR 0 9 "" 0 blank1

2 SQLCHAR 0 15 "" 1 tele_no


3 SQLCHAR 0 4 "" 0 blank2

4 SQLCHAR 0 6 "" 2 qtr_inv_id


5 SQLCHAR 0 7 "" 0 blank3

6 SQLCHAR 0 14 "" 3 total_bill
7 SQLCHAR 0 14 "" 4 local_call
8 SQLCHAR 0 14 "" 5 nat_call
9 SQLCHAR 0 14 "" 6 idd_call
10 SQLCHAR 0 14 "" 7 other_call
11 SQLCHAR 0 8 "" 8 bill_date


12 SQLCHAR 0 4 "" 0 blank4
13 SQLCHAR 0 14 "" 0 blank5

14 SQLCHAR 0 14 "" 9 div_chrg
15 SQLCHAR 0 14 "" 10 adv_chrg
16 SQLCHAR 0 14 "" 11 oth_chrg
17 SQLCHAR 0 14 "" 12 misc_call
18 SQLCHAR 0 14 "" 13 tele_call
19 SQLCHAR 0 14 "" 14 ring_call
20 SQLCHAR 0 14 "" 15 rem_call
21 SQLCHAR 0 14 "" 16 fwd_call
22 SQLCHAR 0 14 "" 17 chrg_call
23 SQLCHAR 0 14 "" 18 oper_call
24 SQLCHAR 0 14 "" 19 chrg_adv
25 SQLCHAR 0 14 "" 20 call_allow
26 SQLCHAR 0 14 "" 21 tw_call
27 SQLCHAR 0 14 "" 22 chrg_amt
28 SQLCHAR 0 14 "" 23 call_disc
29 SQLCHAR 0 14 "\r\n" 24 mobile_call

Note that I also changed the version number for the format to 7.0.

I was able to load your data file with this format file. (With the
disclaimed that the data file had been split up in several lines in
news transport, so I had to recreate it.)

--
Erland Sommarskog, SQL Server MVP, som...@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Robert Mateer

unread,
Jun 20, 2003, 4:58:14 AM6/20/03
to
Erland Sommarskog,
Thank you for your help.
After I posted the problem I worked on it again and managed to solve my
problem.
I have loaded the format file content to help anyone else with a simular
problem.
when I first counted the spaces in the data file I counted 397 which was
wrong, it should have been 400.
The extra field in this copy is to catch all the blank spaces at the end
of the line.
I can see your solution Erland, with the second count 0,2,0,4,0,6 etc
but should be 0,1,0,2,0,3,4 etc, I found it didn't matter but I shall
follow this sequence in future to keep it formal.

6.0
30


1 SQLCHAR 0 9 "" 0 blank1

2 SQLCHAR 0 15 "" 2 tele_no


3 SQLCHAR 0 4 "" 0 blank2

4 SQLCHAR 0 6 "" 4 qtr_inv_id


5 SQLCHAR 0 7 "" 0 blank3

6 SQLCHAR 0 14 "" 6 total_bill
7 SQLCHAR 0 14 "" 7 local_call
8 SQLCHAR 0 14 "" 8 nat_call
9 SQLCHAR 0 14 "" 9 idd_call
10 SQLCHAR 0 14 "" 10 other_call
11 SQLCHAR 0 8 "" 11 bill_date

12 SQLCHAR 0 5 "" 0 blank4
13 SQLCHAR 0 17 "" 0 blank5
14 SQLCHAR 0 14 "" 14 div_chrg
15 SQLCHAR 0 14 "" 15 adv_chrg
16 SQLCHAR 0 14 "" 16 oth_chrg
17 SQLCHAR 0 14 "" 17 misc_call
18 SQLCHAR 0 14 "" 18 tele_call
19 SQLCHAR 0 14 "" 19 ring_call
20 SQLCHAR 0 14 "" 20 rem_call
21 SQLCHAR 0 14 "" 21 fwd_call
22 SQLCHAR 0 14 "" 22 chrg_call
23 SQLCHAR 0 14 "" 23 oper_call
24 SQLCHAR 0 14 "" 24 chrg_adv
25 SQLCHAR 0 14 "" 25 call_allow
26 SQLCHAR 0 14 "" 26 tw_call
27 SQLCHAR 0 14 "" 27 chrg_amt
28 SQLCHAR 0 14 "" 28 call_disc
29 SQLCHAR 0 14 "" 29 mobile_call
30 SQLCHAR 0 35 "\r\n" 0 blank6

Thanks again.
Robert.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Robert Mateer

unread,
Jun 20, 2003, 5:18:05 AM6/20/03
to

Erland Sommarskog,
Ignore what I have just said, the reason why my new format file
worked is because I added the new fields to my Table blank1, blank2,
blank3, blank4 and blank5 so the numbering on my format file would work.
I later removed the blank fields after the data was loaded.
So the real problem as the error described indexing as before adding the
new fields to my table it looked for table field numbers which weren't
there.
Thank Erland and sorry for the miss guide information.
0 new messages