| Luis Marquez 2005-04-29, 11:26 am |
| Hello all,
when running bcp, it happens that some batches do not load at all. This is true (and expected) if fatal-errors are found or if the number of non-fatal errors in the current batch is greater than the MAXERRORS parameter. However, there are some situations where batches seemed to be completely suppresed dependant on the number of consecutive batches with a specific condition.
The scenario is the following:
bcp file volume: 500 or 700 records
cmd: bcp.exe db.dbo.table in C:\Temp\file.bcp -b100 -c -SDBServer -Usa -P -eC:\Temp\file.err
maxerrors: [MAXERRORS -m] parameter not specified. Defaults to 10.
NON-FATAL ERRORS USED:
- Attempt to insert a Null value where not allowed
CTLIB Message: - L0/O0/S0/N0/0/0:
blk_rowxfer(): blk layer: internal BLK-Library error: No value or default value available and NULL not allowed. col = 14
- Type conversion error (e.g. attempting to place a CHAR value into a NUMERIC field)
CSLIB Message: - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
FATAL ERRORS USED:
- Duplicate Keys found. Table contains Index TableIndex1
Server Message: LONSERVER - Msg 2601, Level 14, State 2:
Attempt to insert duplicate key row in object 'TableIndex' with unique index 'TableIndex1'
TESTED SCENARIOS:
SCENARIO 0
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
0
0
100
2
100
0
0
100
3
100
0
0
100
4
100
0
0
100
5
100
0
0
100
Totals
500
0
500 out of 500
SCENARIO 1
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
2
0
98
2
100
0
0
100
3
100
3
0
97
4
100
0
0
100
5
100
0
0
100
Totals
500
5
495 out of 500
NOTE: Counts missing on Batches no 1 and 3 due to Non-Fatal Errors (blue) ---> Expected
SCENARIO 2
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
3
0
97
2
100
2
0
98
3
100
30
0
0
4
100
n/a
n/a
n/a
5
100
n/a
n/a
n/a
Totals
500
35
195 out of 500
NOTE: Supressed Batch (red) due to Non-Fatal Errors > MAXERRORS
NOTE: Bulk Copy process aborts at Batch no 3 due to Non-Fatal Errors > MAXERRORS. Following Batches (grey) automatically ignored. ---> Expected
SCENARIO 3
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
2
1
0
2
100
0
0
100
3
100
3
0
97
4
100
0
0
100
5
100
5
1
0
Totals
500
10
2
297 out of 500
NOTE: Supressed batches due to Fatal Errors (red).
NOTE: Batch 1 & 5 (red) - Non-Fatal Errors are irrelevant if Fatal Errors are found. ---> Expected
SCENARIO 4
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
0
0
100
2
100
2
0
98
3 (*)
100
3
0
97
4
100
0
1
0
5
100
0
0
100
Totals
500
5
1
395 out of 500
NOTE: Last Batch (*) in a row of Batches with Non-Fatal Errors only (blue), followed by another batch (red) with Fatal Errors is loaded if
(1) the number of consecutive Batches with Non-Fatal Errors (blue) is even, e.g. 2, 4, 6, etc.. ---> OK for now
SCENARIO 5 (Cnt'd Scenario 4)
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
0
1
0
2
100
2
0
98
3
100
5
0
95
4
100
3
0
97
5 (*)
100
5
0
95
6
100
0
3
0
7
100
0
0
100
Totals
700
485 out of 700
NOTE: Last Batch (*) in a row of Batches with Non-Fatal Errors only (blue), followed by another batch (red) with Fatal Errors is loaded if
(1) the number of consecutive Batches with Non-Fatal Errors (blue) is even, e.g. 2, 4, 6, etc.. ---> OK for now
SCENARIO 6
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
5
0
95
2
100
2
0
98
3 (*)
100
3
0
0 (!)
4
100
0
1
0
5
100
0
0
100
Totals
500
10
1
293 out of 500
NOTE: Last Batch (*) in a row of Batches with Non-Fatal Errors only (blue), followed by another batch (red) with Fatal Errors is ignored if
(1) the number of consecutive Batches with Non-Fatal Errors (blue) is odd, e.g. 1, 3, 5, etc.. ---> Why??
SCENARIO 7 (Cnt'd Scneario 6)
Batch no
Batch Size
Non-Fatal Errors
Fatal Errors
Loaded Records
1
100
5
0
95
2
100
0
0
100
3 (*)
100
3
0
0 (!)
4
100
0
1
0
5
100
0
0
100
Totals
500
8
1
295 out of 500
NOTE: Last Batch (*) in a row of Batches with Non-Fatal Errors only (blue), followed by another batch (red) with Fatal Errors is ignored if
(1) the number of consecutive Batches with Non-Fatal Errors (blue) is odd, e.g. 1, 3, 5, etc.. ---> Why??
Do you have any suggestions has why this may be happening??
Client running Windows XP v. 5.1 Build 2600 : Service Pack 2 ...
Sybase ASE 12.5.1 running on same machine
H:\>bcp -v returns
Sybase CTBCP Utility/12.5.1 ASE Edition/P-EBF11378/PC Intel/BUILD1251-004/OPT/Thu Aug 14 11:57:18 203
Thanks in advance,
Luis
|