| 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 Key found. The Database table has an Index assigned
Server Message: LONSERVER1 - 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
ue) ---> 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 --->
Expected
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). ---> Expected
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 know why this may be happening? Any suggestions please??
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
|