> *Hi everybody,
>
> I'm trying to BULK INSERT a one file that is in UNICODE format and > it
> does not work. Can someone point me in the right direction ?
>
> That's the FMT:
> -------------------------------
> 8.0
> 16
> 1 SQLNCHAR 0 0 ";" 1 + SQL_Latin1_General_CP1_CI_AI
> 2 SQLNCHAR 0 0 ";" 2 + SQL_Latin1_General_CP1_CI_AI
> 3 SQLNCHAR 0 0 ";" 3 + SQL_Latin1_General_CP1_CI_AI
> 4 SQLNCHAR 0 0 ";" 7 + SQL_Latin1_General_CP1_CI_AI
> 5 SQLNCHAR 0 0 ";" 4 + SQL_Latin1_General_CP1_CI_AI
> 6 SQLNCHAR 0 0 ";" 8 + SQL_Latin1_General_CP1_CI_AI
> 7 SQLNCHAR 0 0 ";" 9 + SQL_Latin1_General_CP1_CI_AI
> 8 SQLNCHAR 0 0 ";" 10 + SQL_Latin1_General_CP1_CI_AI
> 9 SQLNCHAR 0 0 ";" 11 + SQL_Latin1_General_CP1_CI_AI
> 10 SQLNCHAR 0 0 ";" 12 + SQL_Latin1_General_CP1_CI_AI
> 11 SQLNCHAR 0 0 ";" 15 + SQL_Latin1_General_CP1_CI_AI
> 12 SQLNCHAR 0 0 ";" 16 + SQL_Latin1_General_CP1_CI_AI
> 13 SQLNCHAR 0 0 ";" 14 + SQL_Latin1_General_CP1_CI_AI
> 14 SQLNCHAR 0 0 ";" 17 + SQL_Latin1_General_CP1_CI_AI
> 15 SQLNCHAR 0 0 ";" 18 + SQL_Latin1_General_CP1_CI_AI
> 16 SQLNCHAR 0 0 "\r\n" 0 + SQL_Latin1_General_CP1_CI_AI
> --------------------------------
>
> That's an extract of the file:
> ------------------------------------
> 01;0031;794000;1112489;AGBREF;NF;71;0;;;;1;11.95;;20050308;
> 01;0031;794001;2158743389;PORUJIK;NF;71;0;;;;1;2.6;;20050308;
> 01;0031;794002;211118877;BILBOKA;NF;71;0;;;;1;4.95;;20050308;
> ------------------------------------
>
> I want to get the whole thing as varchar as some columns may > potentially
> have numbers or text or some. else...
>
> That's my table def:
> ----------------------------
> CREATE TABLE [dbo].[DHM_SpecDennerArticlesUC] (
> [sca_RecordType] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_DataProvider] [nvarchar] (6) COLLATE > SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_ArticleNumber] [nvarchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_Article] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Category] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ,
> [sca_EAN] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ,
> [sca_CategoryLevel01] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel02] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel03] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel04] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel05] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel06] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_Price] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ,
> [sca_Field01] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Field02] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Field03] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Field04] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Field05] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Field06] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ,
> [sca_Status] [int] NULL
> ) ON [PRIMARY]
> ----------------------------
>
> And that's my BULK INSERT query:
> -------------------
> bulk insert MyTable from '\\server\share\FILE_UNICODE.txt' with
> (formatfile='\\server\share\Server\Format Files\MyFile.fmt',
> datafiletype='widechar')
> -------------------
>
> And best of all, here's the error I get:
> -------------------
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, > column
> 16. 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.
> -------------------
>
> Can someone help me out on this ? I've been using bulk insert for > quite
> a long period of time (never with unicode thought) but it always > worked
> out. USING SQL Server 2000, SP3.
>
> Thanks,
> Nick * -- nmaseko ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message1601966.html

> *Hi everybody,
>
> I'm trying to BULK INSERT a one file that is in UNICODE format and
> it
> does not work. Can someone point me in the right direction ?
>
> That's the FMT:
> -------------------------------
> 8.0
> 16
> 1 SQLNCHAR 0 0 ";" 1 + SQL_Latin1_General_CP1_CI_AI
> 2 SQLNCHAR 0 0 ";" 2 + SQL_Latin1_General_CP1_CI_AI
> 3 SQLNCHAR 0 0 ";" 3 + SQL_Latin1_General_CP1_CI_AI
> 4 SQLNCHAR 0 0 ";" 7 + SQL_Latin1_General_CP1_CI_AI
> 5 SQLNCHAR 0 0 ";" 4 + SQL_Latin1_General_CP1_CI_AI
> 6 SQLNCHAR 0 0 ";" 8 + SQL_Latin1_General_CP1_CI_AI
> 7 SQLNCHAR 0 0 ";" 9 + SQL_Latin1_General_CP1_CI_AI
> 8 SQLNCHAR 0 0 ";" 10 + SQL_Latin1_General_CP1_CI_AI
> 9 SQLNCHAR 0 0 ";" 11 + SQL_Latin1_General_CP1_CI_AI
> 10 SQLNCHAR 0 0 ";" 12 + SQL_Latin1_General_CP1_CI_AI
> 11 SQLNCHAR 0 0 ";" 15 + SQL_Latin1_General_CP1_CI_AI
> 12 SQLNCHAR 0 0 ";" 16 + SQL_Latin1_General_CP1_CI_AI
> 13 SQLNCHAR 0 0 ";" 14 + SQL_Latin1_General_CP1_CI_AI
> 14 SQLNCHAR 0 0 ";" 17 + SQL_Latin1_General_CP1_CI_AI
> 15 SQLNCHAR 0 0 ";" 18 + SQL_Latin1_General_CP1_CI_AI
> 16 SQLNCHAR 0 0 "\r\n" 0 + SQL_Latin1_General_CP1_CI_AI
> --------------------------------
>
> That's an extract of the file:
> ------------------------------------
> 01;0031;794000;1112489;AGBREF;NF;71;0;;;;1;11.95;;20050308;
> 01;0031;794001;2158743389;PORUJIK;NF;71;0;;;;1;2.6;;20050308;
> 01;0031;794002;211118877;BILBOKA;NF;71;0;;;;1;4.95;;20050308;
> ------------------------------------
>
> I want to get the whole thing as varchar as some columns may
> potentially
> have numbers or text or some. else...
>
> That's my table def:
> ----------------------------
> CREATE TABLE [dbo].[DHM_SpecDennerArticlesUC] (
> [sca_RecordType] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_DataProvider] [nvarchar] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_ArticleNumber] [nvarchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_Article] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Category] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [sca_EAN] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [sca_CategoryLevel01] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel02] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel03] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel04] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel05] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_CategoryLevel06] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [sca_Price] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [sca_Field01] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Field02] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Field03] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Field04] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Field05] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Field06] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [sca_Status] [int] NULL
> ) ON [PRIMARY]
> ----------------------------
>
> And that's my BULK INSERT query:
> -------------------
> bulk insert MyTable from '\\server\share\FILE_UNICODE.txt' with
> (formatfile='\\server\share\Server\Format Files\MyFile.fmt',
> datafiletype='widechar')
> -------------------
>
> And best of all, here's the error I get:
> -------------------
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column
> 16. 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.
> -------------------
>
> Can someone help me out on this ? I've been using bulk insert for
> quite
> a long period of time (never with unicode thought) but it always
> worked
> out. USING SQL Server 2000, SP3.
>
> Thanks,
> Nick *