Problem with BULK INSERT in SQL Server 2005 Express

Hi, group
I'm having a problem when trying to use 'BULK INSERT' to import data from a
plain file to a SQL Server 2005 Express database; i have simplified both
the table and the data file, but i'm still unable to understand the
error... I'm using a (simplified) format file, specifically this one:
9.0
2
1 SQLINT 0 4 ";" 1 Id
""
2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The (simplified) data file i'm trying to import have now only one line:
1;8015
(his content, as seen in a hex editor, is exactly 313B383031350D0A)
The (simplified) database is being created with this:
create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
However, when i execute this command:
BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
(FORMATFILE='D:\access\articulos.fmt')
i'm getting the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1,
column 2. 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)".
I have done some tests changing the terminators, adding more lines,
changing the size of the fields, but they have failed and, what is worse, i
don't understand where is the problem
Does anyone know what is happening? Any help would be welcomed...
Jose Luis BravoJose,
You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
Try this format file:
9.0
2
1 SQLINT 0 4 ";" 1 Id ""
2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The prefix length column is used for native format imports, not text
file imports.
(This information is harder to find in the 2005 documentation,
unfortunately.)
Steve Kass
Drew University
Jose Luis Bravo wrote:

>Hi, group
>I'm having a problem when trying to use 'BULK INSERT' to import data from a
>plain file to a SQL Server 2005 Express database; i have simplified both
>the table and the data file, but i'm still unable to understand the
>error... I'm using a (simplified) format file, specifically this one:
>9.0
>2
>1 SQLINT 0 4 ";" 1 Id
>""
>2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
>SQL_Latin1_General_CP1_CI_AS
>
>The (simplified) data file i'm trying to import have now only one line:
>1;8015
>(his content, as seen in a hex editor, is exactly 313B383031350D0A)
>The (simplified) database is being created with this:
>create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
>
>However, when i execute this command:
>BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
>(FORMATFILE='D:\access\articulos.fmt')
>i'm getting the following error:
>Msg 4866, Level 16, State 7, Line 1
>The bulk load failed. The column is too long in the data file for row 1,
>column 2. 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)".
>
>I have done some tests changing the terminators, adding more lines,
>changing the size of the fields, but they have failed and, what is worse, i
>don't understand where is the problem
>
>Does anyone know what is happening? Any help would be welcomed...
>
>Jose Luis Bravo
>|||El Wed, 08 Mar 2006 08:54:09 -0500, Steve Kass escribi:
Thanks, Steve! Effectively, i hadn't seen that information in the docs but,
as i had made the original format file with the bcp utility, i incorrectly
supposed it should be OK.
Thanks again.

> Jose,
> You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
> Try this format file:
> 9.0
> 2
> 1 SQLINT 0 4 ";" 1 Id ""
> 2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
> SQL_Latin1_General_CP1_CI_AS
> The prefix length column is used for native format imports, not text
> file imports.
> (This information is harder to find in the 2005 documentation,
> unfortunately.)
> Steve Kass
> Drew University