bcombe
asked on
Cannot perform bulk insert. Invalid collation name
I am trying to do the bulk insert with a format file and getting the following error:
Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'C:\MLFPformat_mod.fmt'.
The structure of the table is:
Column 0 identity not null
Column 1 varchar(255) null
Column 2 varchar(255) null
Column 4 varchar(255) null
The format file has the following:
8.0
4
1 SQLCHAR 0 0 "" 0 ExtraColumn ""
2 SQLCHAR 0 255 "," 1 [Column 1] SQL_Latin1_General_CP1_CI_
3 SQLCHAR 0 255 "," 2 [Column 2] SQL_Latin1_General_CP1_CI_
4 SQLCHAR 0 255 "\r\n" 3 [Column 4] SQL_Latin1_General_CP1_CI_
The sample rows from data file that i try to insert are:
01-35470001,D,/scanned docs/5-31-07/01-3547/01-35
01-35470002,D,/scanned docs/5-31-07/01-3547/01-35
01-35470003,D,/scanned docs/5-31-07/01-3547/01-35
02-23500001,D,/scanned docs/5-31-07/02-2350/02-23
I am trying to run the Bulk insert command
BULK INSERT MasterLFP FROM 'C:\MasterLFP.txt' WITH (FORMATFILE = 'C:\MLFPformat_mod.fmt'); and getting the above error.
Any help from the experts!
Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'C:\MLFPformat_mod.fmt'.
The structure of the table is:
Column 0 identity not null
Column 1 varchar(255) null
Column 2 varchar(255) null
Column 4 varchar(255) null
The format file has the following:
8.0
4
1 SQLCHAR 0 0 "" 0 ExtraColumn ""
2 SQLCHAR 0 255 "," 1 [Column 1] SQL_Latin1_General_CP1_CI_
3 SQLCHAR 0 255 "," 2 [Column 2] SQL_Latin1_General_CP1_CI_
4 SQLCHAR 0 255 "\r\n" 3 [Column 4] SQL_Latin1_General_CP1_CI_
The sample rows from data file that i try to insert are:
01-35470001,D,/scanned docs/5-31-07/01-3547/01-35
01-35470002,D,/scanned docs/5-31-07/01-3547/01-35
01-35470003,D,/scanned docs/5-31-07/01-3547/01-35
02-23500001,D,/scanned docs/5-31-07/02-2350/02-23
I am trying to run the Bulk insert command
BULK INSERT MasterLFP FROM 'C:\MasterLFP.txt' WITH (FORMATFILE = 'C:\MLFPformat_mod.fmt'); and getting the above error.
Any help from the experts!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the reply. I tried to put the actual column name instead of ExtraColumn and still get the error but with the Column 1 (Cannot perform bulk insert. Invalid collation name for source column 1 in format file 'C:\MLFPformat_mod.fmt'.).
I use version 8.0 only. I don't have Column 3 in the database table.