January 19, 2009 at 10:41 pm
Here is my sample data:
1;a;b;c;; 2;d;e;f;; 3;g;h;i;;
4;j;k;l;; 5;m;n;o;;
6;p;q;r;;
Here is my sample format file (BCP 9):
9.0
7
1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS
7 SQLCHAR 0 0 "\r" 0 x Latin1_General_CI_AS
Problem is that when I used "\r", only 1, 4, 6 is save not all, but when I try to use "\0", only first record is save.
How I am gonna solve this problem?
Thanks!
January 20, 2009 at 6:58 am
Try adding "/r/n" as row terminator (replace "/" with "\")....
--Ramesh
January 20, 2009 at 5:46 pm
This is the error message display after executing this sql statement,
BULK INSERT VSXCDRI.dbo.SansayRawInfo FROM 'C:\20090114-0554-87726770-87726788.CDR' WITH (FORMATFILE = 'C:\SansayRawInfoFormat6.FMT')
This is the format file:
9.0
61
1 SQLCHAR 0 0 "" 0 RawDataId SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 ";" 2 Sequence SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 ";" 3 Version SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 44 ";" 4 RecordType SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 26 ";" 5 ConnectionType SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 42 ";" 6 SessionID SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 14 ";" 7 ReleaseCause SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 42 ";" 8 StartTime SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 42 ";" 9 AnswerTime SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 42 ";" 10 ReleaseTOD SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 42 ";" 11 WestofGMT SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 42 ";" 12 RelCauseTxt SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 28 ";" 13 RelCauseBin SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 22 ";" 14 1strelease SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 64 ";" 15 OrgTrunkId SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 16 ";" 16 OrgProtocol SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 138 ";" 17 OrgSrcNo SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 138 ";" 18 OrgSrcHost SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 138 ";" 19 OrgDestNo SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 138 ";" 20 OrgDestHost SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 138 ";" 21 OrgCallID SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 26 ";" 22 OrgRemPayIPAdd SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 16 ";" 23 OrgRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 26 ";" 24 OrgLocPayIPAdd SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 16 ";" 25 OrgLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 138 ";" 26 OrgCodecList SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 20 ";" 27 OrgIngrPck SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 20 ";" 28 OrgEgrPck SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 20 ";" 29 OrgIngrOct SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 20 ";" 30 OrgEgrOct SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 20 ";" 31 OrgIngrPckLoss SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 20 ";" 32 OrgIngrDelay SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 20 ";" 33 OrgIngrPckJitter SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 64 ";" 34 TermTrunkId SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 16 ";" 35 TermProtocol SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 138 ";" 36 TermSrcNo SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 138 ";" 37 TermSrcHost SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 138 ";" 38 TermDestNo SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 138 ";" 39 TermDestHost SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 138 ";" 40 TermCallID SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 26 ";" 41 TermRemPayIPAdd SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 16 ";" 42 TermRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 26 ";" 43 TermLocPayIPAdd SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 16 ";" 44 TermLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 138 ";" 45 TermCodecList SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 20 ";" 46 TermIngrPck SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 20 ";" 47 TermEgrPck SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 20 ";" 48 TermIngrOct SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 20 ";" 49 TermEgrOct SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 20 ";" 50 TermIngrPckLoss SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 20 ";" 51 TermIngrDelay SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 20 ";" 52 TermIngrPckJitter SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 44 ";" 53 FinRouteInd SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 74 ";" 54 RoutingDigits SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 16 ";" 55 CallDuration SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 16 ";" 56 PostDialDelay SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 16 ";" 57 RingTime SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 20 ";" 58 Duration SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 46 ";" 59 ConfID SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 74 ";" 60 RPIDANI SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 0 "/r/n" 0 Status SQL_Latin1_General_CP1_CI_AS
Error Message:
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 60. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply