Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1SQLCHAR011""0IDSQL_Latin1_General_CP1_CI_AS
2SQLCHAR075""1EmailSQL_Latin1_General_CP1_CI_AS
3SQLCHAR08"\r\n"0TimeStampSQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegr oups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>
|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal
|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegro ups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>