Asp Forum - BULK INSERT, "String or binary data would be truncated."

brian.twardzik

3/21/2007 3:10:00 PM

The Format File [_format.txt]

8.0
9
1 SQLCHAR 0 0 "|" 1 col1 ""
2 SQLCHAR 0 0 "|" 2 col2 ""
3 SQLINT 0 0 "|" 3 col3 ""
4 SQLCHAR 0 0 "|" 4 col4 ""
5 SQLCHAR 0 0 "|" 5 col5 ""
6 SQLCHAR 0 0 "|" 6 col6 ""
7 SQLCHAR 0 0 "|" 7 col7 ""
8 SQLCHAR 0 0 "|" 8 col8 ""
9 SQLCHAR 0 0 "|" 9 col9 ""

The Data File [bulk.txt]

|AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
|AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
|AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
|AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
|AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|

Error:

Event Type: Error

Description:
System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
[ODBC SQL Server Driver][SQL Server]String or binary data would be
truncated.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
at SQLDMO._Database.ExecuteWithResults(String Command, Object
Length)
at {_somewhere_in_my_code_}

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/....

Initial Query

Event Type: Warning
Description:
BULK INSERT _table_ FROM '_format.txt', KEEPNULLS)

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/....

9 Answers

brian.twardzik

3/21/2007 3:11:00 PM

On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> The Format File [_format.txt]
>
> 8.0
> 9
> 1 SQLCHAR 0 0 "|" 1 col1 ""
> 2 SQLCHAR 0 0 "|" 2 col2 ""
> 3 SQLINT 0 0 "|" 3 col3 ""
> 4 SQLCHAR 0 0 "|" 4 col4 ""
> 5 SQLCHAR 0 0 "|" 5 col5 ""
> 6 SQLCHAR 0 0 "|" 6 col6 ""
> 7 SQLCHAR 0 0 "|" 7 col7 ""
> 8 SQLCHAR 0 0 "|" 8 col8 ""
> 9 SQLCHAR 0 0 "|" 9 col9 ""
>
> The Data File [bulk.txt]
>
> |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
>
> Error:
>
> Event Type: Error
>
> Description:
> System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> [ODBC SQL Server Driver][SQL Server]String or binary data would be
> truncated.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> at SQLDMO._Database.ExecuteWithResults(String Command, Object
> Length)
> at {_somewhere_in_my_code_}
>
> For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> Initial Query
>
> Event Type: Warning
> Description:
> BULK INSERT _table_ FROM '_format.txt', KEEPNULLS)
>
> For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....

I really have no idea why its giving the error. I put a 0 value for
the host file data length because the MSDN documentation said it would
figure it out on the fly!

bob

3/21/2007 3:55:00 PM

I got this to work with your sample data:

CREATE TABLE #tmp(
col1 VARCHAR(50),
col2 VARCHAR(50),
col3 VARCHAR(50),
col4 VARCHAR(50),
col5 VARCHAR(50),
col6 VARCHAR(50),
col8 VARCHAR(50),
col7 VARCHAR(50)
)
GO

BULK INSERT #tmp FROM 'c:\temp\temp.txt'
WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )

As your data row starts with a pipe, that counts as a field.
Let me know how you get on.

wBob

"brian.twardzik@usask.ca" wrote:

> On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > The Format File [_format.txt]
> >
> > 8.0
> > 9
> > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > 3 SQLINT 0 0 "|" 3 col3 ""
> > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > 9 SQLCHAR 0 0 "|" 9 col9 ""
> >
> > The Data File [bulk.txt]
> >
> > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
> >
> > Error:
> >
> > Event Type: Error
> >
> > Description:
> > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]String or binary data would be
> > truncated.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> > at SQLDMO._Database.ExecuteWithResults(String Command, Object
> > Length)
> > at {_somewhere_in_my_code_}
> >
> > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> >
> > Initial Query
> >
> > Event Type: Warning
> > Description:
> > BULK INSERT _table_ FROM '_format.txt', KEEPNULLS)
> >
> > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> I really have no idea why its giving the error. I put a 0 value for
> the host file data length because the MSDN documentation said it would
> figure it out on the fly!
>
>

Robert Klemme

3/21/2007 3:56:00 PM

On 21.03.2007 16:10, brian.twardzik@usask.ca wrote:
> On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
>> The Format File [_format.txt]
>>
>> 8.0
>> 9
>> 1 SQLCHAR 0 0 "|" 1 col1 ""
>> 2 SQLCHAR 0 0 "|" 2 col2 ""
>> 3 SQLINT 0 0 "|" 3 col3 ""
>> 4 SQLCHAR 0 0 "|" 4 col4 ""
>> 5 SQLCHAR 0 0 "|" 5 col5 ""
>> 6 SQLCHAR 0 0 "|" 6 col6 ""
>> 7 SQLCHAR 0 0 "|" 7 col7 ""
>> 8 SQLCHAR 0 0 "|" 8 col8 ""
>> 9 SQLCHAR 0 0 "|" 9 col9 ""
>>
>> The Data File [bulk.txt]
>>
>> |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
>> |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
>> |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
>>
>> Error:
>>
>> Event Type: Error
>>
>> Description:
>> System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
>> [ODBC SQL Server Driver][SQL Server]String or binary data would be
>> truncated.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> terminated.
>> at SQLDMO._Database.ExecuteWithResults(String Command, Object
>> Length)
>> at {_somewhere_in_my_code_}
>>
>> For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>>
>> Initial Query
>>
>> Event Type: Warning
>> Description:
>> BULK INSERT _table_ FROM '_format.txt', KEEPNULLS)
>>
>> For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> I really have no idea why its giving the error. I put a 0 value for
> the host file data length because the MSDN documentation said it would
> figure it out on the fly!
>

Yeah, but maybe your table's fields are not wide enough.

robert

brian.twardzik

3/22/2007 11:04:00 PM

On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> I got this to work with your sampledata:
>
> CREATE TABLE #tmp(
> col1 VARCHAR(50),
> col2 VARCHAR(50),
> col3 VARCHAR(50),
> col4 VARCHAR(50),
> col5 VARCHAR(50),
> col6 VARCHAR(50),
> col8 VARCHAR(50),
> col7 VARCHAR(50)
> )
> GO
>
> BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
>
> As yourdatarow starts with a pipe, that counts as a field.
> Let me know how you get on.
>
> wBob
>
> "brian.tward...@usask.ca" wrote:
> > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > The Format File [_format.txt]
>
> > > 8.0
> > > 9
> > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > 9 SQLCHAR 0 0 "|" 9 col9 ""
>
> > > TheDataFile [bulk.txt]
>
> > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
>
> > > Error:
>
> > > Event Type: Error
>
> > > Description:
> > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > >truncated.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > terminated.
> > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > Length)
> > > at {_somewhere_in_my_code_}
>
> > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > > Initial Query
>
> > > Event Type: Warning
> > > Description:
> > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
>
> > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > I really have no idea why its giving the error. I put a 0 value for
> > the host filedatalength because the MSDN documentation said itwould
> > figure it out on the fly!

I've been fighting with this particular beast for days. The errors
seem to change with slight modifications that I don't quite grasp. I
also tend to run into a lot of Unexpected EOF errors as well. If the
data contains characters above 127 I get this error. I tried the
CODEPAGE = 'ACD' flag, same result. To rule out some cases I'll say
this:

1) The data is never over-sized for the table. The data came from the
original table, and is destined to be inside a recreation of that same
table. Hence, any data going into the destination table simply cannot
be too big.
2) The data can contain 'strange characters' stuff beyond 127. As I
understand it, if I set the CODEPAGE hint in the BULK INSERT
statement, all should be well. It does not help. I've tried to use
unicode files. They are another dimension of pain and suffering that I
will not fully detail here.
3) For simplicity's sake, any column may be null.

Overall, BULK INSERT may be the worst SQL command I have ever had the
misfortune of trying to use. Yet, here I am, still trying to use it. I
need a programmable solution for moving millions of rows. I looked
into the SQLDMO Transfer object, but it seems to want to move the
entire contents of an object, and I want to be able to limit what I
end up moving around. The DTS wizard isn't an option.

I'd rather not dump the stuff into a staging table, because I fear
what that will do to memory consumption and performance of the
destination server. And yes, I've tried dumping into temp tables,
regular precreated tables, all with varchar columns of varying
lengths, some with no max specified at all. All generate an error at
some point.

As of the time I am writing this post, I expect my tone to be full
of a-typical usenet bitterness. I'm tired, frustrated, and having been
introduced to the 'definition of insanity', that being to try the same
thing expecting different results, I am beginning to believe that the
code of BULK INSERT should be printed on thousand year old parchment,
exoricised, hexed, split up to the four corners of the earth and
ritualistically burned in a pyre fueled only by the most ancient of
trees who may have the will to crush this demonic query.

bob

3/23/2007 10:13:00 AM

I did notice your BULK INSERT statement said FROM '_format.txt' which sounds
like your format file, when it should be the data file.

BULK INSERT is not so bad. The thing to do is start with one row. Hack a
sample of your datasource down to one row and get that working. This will
help you button down your issues in a more manageable form. Then do 10, 100,
10,000 etc.

If you can get that going, then you know the issue is with the data, and not
with BULK INSERT. Narrow it down to work out exactly which rows are failing.
Bulk insert may give you a line number, or even a column. Open your data in
a raw editor and goto that line. Hack it out on its own into a text file.
Make it work.

One other alternative, similar to the staging option you mentioned, is to
import all data as one row, ie put the FIELDTERMINATOR as something not in
the file, like @. Create one table with one very wide column. Don't worry
about memory, just make sure tempdb is nice and big.

Then you can use more detailed SQL code to split it out, once you've got it
into the database.

Finally, have you tired the native file option when outputting the data?
You don't need a format file then. That might help.

Stick with it! Don't give up.

Let me know how you get on.

wBob

"brian.twardzik@usask.ca" wrote:

> On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> > I got this to work with your sampledata:
> >
> > CREATE TABLE #tmp(
> > col1 VARCHAR(50),
> > col2 VARCHAR(50),
> > col3 VARCHAR(50),
> > col4 VARCHAR(50),
> > col5 VARCHAR(50),
> > col6 VARCHAR(50),
> > col8 VARCHAR(50),
> > col7 VARCHAR(50)
> > )
> > GO
> >
> > BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> > WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
> >
> > As yourdatarow starts with a pipe, that counts as a field.
> > Let me know how you get on.
> >
> > wBob
> >
> > "brian.tward...@usask.ca" wrote:
> > > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > > The Format File [_format.txt]
> >
> > > > 8.0
> > > > 9
> > > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > > 9 SQLCHAR 0 0 "|" 9 col9 ""
> >
> > > > TheDataFile [bulk.txt]
> >
> > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
> >
> > > > Error:
> >
> > > > Event Type: Error
> >
> > > > Description:
> > > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > > >truncated.
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > terminated.
> > > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > > Length)
> > > > at {_somewhere_in_my_code_}
> >
> > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> >
> > > > Initial Query
> >
> > > > Event Type: Warning
> > > > Description:
> > > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
> >
> > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> >
> > > I really have no idea why its giving the error. I put a 0 value for
> > > the host filedatalength because the MSDN documentation said itwould
> > > figure it out on the fly!
>
> I've been fighting with this particular beast for days. The errors
> seem to change with slight modifications that I don't quite grasp. I
> also tend to run into a lot of Unexpected EOF errors as well. If the
> data contains characters above 127 I get this error. I tried the
> CODEPAGE = 'ACD' flag, same result. To rule out some cases I'll say
> this:
>
> 1) The data is never over-sized for the table. The data came from the
> original table, and is destined to be inside a recreation of that same
> table. Hence, any data going into the destination table simply cannot
> be too big.
> 2) The data can contain 'strange characters' stuff beyond 127. As I
> understand it, if I set the CODEPAGE hint in the BULK INSERT
> statement, all should be well. It does not help. I've tried to use
> unicode files. They are another dimension of pain and suffering that I
> will not fully detail here.
> 3) For simplicity's sake, any column may be null.
>
> Overall, BULK INSERT may be the worst SQL command I have ever had the
> misfortune of trying to use. Yet, here I am, still trying to use it. I
> need a programmable solution for moving millions of rows. I looked
> into the SQLDMO Transfer object, but it seems to want to move the
> entire contents of an object, and I want to be able to limit what I
> end up moving around. The DTS wizard isn't an option.
>
> I'd rather not dump the stuff into a staging table, because I fear
> what that will do to memory consumption and performance of the
> destination server. And yes, I've tried dumping into temp tables,
> regular precreated tables, all with varchar columns of varying
> lengths, some with no max specified at all. All generate an error at
> some point.
>
> As of the time I am writing this post, I expect my tone to be full
> of a-typical usenet bitterness. I'm tired, frustrated, and having been
> introduced to the 'definition of insanity', that being to try the same
> thing expecting different results, I am beginning to believe that the
> code of BULK INSERT should be printed on thousand year old parchment,
> exoricised, hexed, split up to the four corners of the earth and
> ritualistically burned in a pyre fueled only by the most ancient of
> trees who may have the will to crush this demonic query.
>
>

brian.twardzik

3/23/2007 2:03:00 PM

Bob wrote:
> I did notice your BULK INSERT statement said FROM '_format.txt' which sounds
> like your format file, when it should be the data file.
>
> BULK INSERT is not so bad. The thing to do is start with one row. Hack a
> sample of your datasource down to one row and get that working. This will
> help you button down your issues in a more manageable form. Then do 10, 100,
> 10,000 etc.
>
> If you can get that going, then you know the issue is with the data, and not
> with BULK INSERT. Narrow it down to work out exactly which rows are failing.
> Bulk insert may give you a line number, or even a column. Open your data in
> a raw editor and goto that line. Hack it out on its own into a text file.
> Make it work.
>
> One other alternative, similar to the staging option you mentioned, is to
> import all data as one row, ie put the FIELDTERMINATOR as something not in
> the file, like @. Create one table with one very wide column. Don't worry
> about memory, just make sure tempdb is nice and big.
>
> Then you can use more detailed SQL code to split it out, once you've got it
> into the database.
>
> Finally, have you tired the native file option when outputting the data?
> You don't need a format file then. That might help.
>
> Stick with it! Don't give up.
>
> Let me know how you get on.
>
> wBob
>
>
>
>
> "brian.twardzik@usask.ca" wrote:
>
> > On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> > > I got this to work with your sampledata:
> > >
> > > CREATE TABLE #tmp(
> > > col1 VARCHAR(50),
> > > col2 VARCHAR(50),
> > > col3 VARCHAR(50),
> > > col4 VARCHAR(50),
> > > col5 VARCHAR(50),
> > > col6 VARCHAR(50),
> > > col8 VARCHAR(50),
> > > col7 VARCHAR(50)
> > > )
> > > GO
> > >
> > > BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> > > WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
> > >
> > > As yourdatarow starts with a pipe, that counts as a field.
> > > Let me know how you get on.
> > >
> > > wBob
> > >
> > > "brian.tward...@usask.ca" wrote:
> > > > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > > > The Format File [_format.txt]
> > >
> > > > > 8.0
> > > > > 9
> > > > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > > > 9 SQLCHAR 0 0 "|" 9 col9 ""
> > >
> > > > > TheDataFile [bulk.txt]
> > >
> > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
> > >
> > > > > Error:
> > >
> > > > > Event Type: Error
> > >
> > > > > Description:
> > > > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > > > >truncated.
> > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > terminated.
> > > > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > > > Length)
> > > > > at {_somewhere_in_my_code_}
> > >
> > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> > >
> > > > > Initial Query
> > >
> > > > > Event Type: Warning
> > > > > Description:
> > > > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
> > >
> > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> > >
> > > > I really have no idea why its giving the error. I put a 0 value for
> > > > the host filedatalength because the MSDN documentation said itwould
> > > > figure it out on the fly!
> >
> > I've been fighting with this particular beast for days. The errors
> > seem to change with slight modifications that I don't quite grasp. I
> > also tend to run into a lot of Unexpected EOF errors as well. If the
> > data contains characters above 127 I get this error. I tried the
> > CODEPAGE = 'ACD' flag, same result. To rule out some cases I'll say
> > this:
> >
> > 1) The data is never over-sized for the table. The data came from the
> > original table, and is destined to be inside a recreation of that same
> > table. Hence, any data going into the destination table simply cannot
> > be too big.
> > 2) The data can contain 'strange characters' stuff beyond 127. As I
> > understand it, if I set the CODEPAGE hint in the BULK INSERT
> > statement, all should be well. It does not help. I've tried to use
> > unicode files. They are another dimension of pain and suffering that I
> > will not fully detail here.
> > 3) For simplicity's sake, any column may be null.
> >
> > Overall, BULK INSERT may be the worst SQL command I have ever had the
> > misfortune of trying to use. Yet, here I am, still trying to use it. I
> > need a programmable solution for moving millions of rows. I looked
> > into the SQLDMO Transfer object, but it seems to want to move the
> > entire contents of an object, and I want to be able to limit what I
> > end up moving around. The DTS wizard isn't an option.
> >
> > I'd rather not dump the stuff into a staging table, because I fear
> > what that will do to memory consumption and performance of the
> > destination server. And yes, I've tried dumping into temp tables,
> > regular precreated tables, all with varchar columns of varying
> > lengths, some with no max specified at all. All generate an error at
> > some point.
> >
> > As of the time I am writing this post, I expect my tone to be full
> > of a-typical usenet bitterness. I'm tired, frustrated, and having been
> > introduced to the 'definition of insanity', that being to try the same
> > thing expecting different results, I am beginning to believe that the
> > code of BULK INSERT should be printed on thousand year old parchment,
> > exoricised, hexed, split up to the four corners of the earth and
> > ritualistically burned in a pyre fueled only by the most ancient of
> > trees who may have the will to crush this demonic query.
> >
> >

Reading back it seems that in my attempts to hide any in-house data I
sufficiently mangled the query.
It should have looked something like this:
BULK INSERT event FROM 'event_bulk.txt' WITH (FORMATFILE =
'event_format.txt')

The 'bulk' files -- the data to be imported through bulk insert -- are
generated by my own application upon receiving a dataset remotely. I
am unaware of how to output the native format ( I believe its
proprietary ?).

Most rows work with the format file:

8.0
4
1 SQLCHAR 0 0 "|" 1 descr RAW
2 SQLCHAR 0 0 "|" 2 event_id RAW
3 SQLCHAR 0 0 "|" 3 priority RAW
4 SQLCHAR 0 0 "~~" 4 servtype_id RAW
{return line}

But when I reach a row with a char(127+) the import procedure produces
this error:

System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
[ODBC SQL Server Driver][SQL Server]String or binary data would be
truncated.

I've tried using unicode files, but that doesn't seem to help. In
fact, it produces -more- of those errors (and yep, I've been sure to
specify the correct DATAFILETYPE hint). Even if I try the CODEPAGE =
'ACP' hint, the error still occurs. :(

Additionally, this error seems to appear where other format files have
no problem:

System.Runtime.InteropServices.COMException (0x800412DD): [Microsoft]
[ODBC SQL Server Driver][SQL Server]Could not bulk insert. Error
reading destination table column name for source column 18 in format
file 'C:\Documents and Settings\twarb1\My Documents\Visual Studio
2005\Projects\Testing SDR Subscriber Service\Testing SDR Subscriber
Service\bin\Debug\site_format.txt'.

And here's site_format.txt:

8.0
18
1 SQLCHAR 0 0 "|" 1 city_name RAW
2 SQLCHAR 0 0 "|" 2 codeword1 RAW
3 SQLCHAR 0 0 "|" 3 codeword2 RAW
4 SQLCHAR 0 0 "|" 4 corpacct_servco_no RAW
5 SQLCHAR 0 0 "|" 5 country_name RAW
6 SQLCHAR 0 0 "|" 6 dst_no RAW
7 SQLCHAR 0 0 "|" 7 install_servco_no RAW
8 SQLCHAR 0 0 "|" 8 phone1 RAW
9 SQLCHAR 0 0 "|" 9 phone2 RAW
10 SQLCHAR 0 0 "|" 10 servco_no RAW
11 SQLCHAR 0 0 "|" 11 site_addr1 RAW
12 SQLCHAR 0 0 "|" 12 site_addr2 RAW
13 SQLCHAR 0 0 "|" 13 site_name RAW
14 SQLCHAR 0 0 "|" 14 site_no RAW
15 SQLCHAR 0 0 "|" 15 sitestat_id RAW
16 SQLCHAR 0 0 "|" 16 sitetype_id RAW
17 SQLCHAR 0 0 "|" 17 state_id RAW
18 SQLCHAR 0 0 "~~" 18 zip_code RAW
{return line}

Now, its the same type of format file... yet I get the error for it
and not for the various other tables that came before. The
inconsistancy is what grinds my gears. The error isn't obvious or it
would appear in every format file, with every data file.

brian.twardzik

3/23/2007 3:48:00 PM

On Mar 23, 8:02 am, brian.tward...@usask.ca wrote:
> Bob wrote:
> > I did notice yourBULKINSERTstatement said FROM '_format.txt' which sounds
> > like your format file, when it should be thedatafile.
>
> >BULKINSERTis not so bad. The thing to do is start with one row. Hack a
> > sample of your datasource down to one row and get that working. This will
> > help you button down your issues in a more manageable form. Then do 10, 100,
> > 10,000 etc.
>
> > If you can get that going, then you know the issue is with thedata, and not
> > withBULKINSERT. Narrow it down to work out exactly which rows are failing.
> > Bulkinsertmay give you a line number, or even a column. Open yourdatain
> > a raw editor and goto that line. Hack it out on its own into a text file.
> > Make it work.
>
> > One other alternative, similar to the staging option you mentioned, is to
> > import alldataas one row, ie put the FIELDTERMINATOR as something not in
> > the file, like @. Create one table with one very wide column. Don't worry
> > about memory, just make sure tempdb is nice and big.
>
> > Then you can use more detailed SQL code to split it out, once you've got it
> > into the database.
>
> > Finally, have you tired the native file option when outputting thedata?
> > You don't need a format file then. That might help.
>
> > Stick with it! Don't give up.
>
> > Let me know how you get on.
>
> > wBob
>
> > "brian.tward...@usask.ca" wrote:
>
> > > On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> > > > I got this to work with your sampledata:
>
> > > > CREATE TABLE #tmp(
> > > > col1 VARCHAR(50),
> > > > col2 VARCHAR(50),
> > > > col3 VARCHAR(50),
> > > > col4 VARCHAR(50),
> > > > col5 VARCHAR(50),
> > > > col6 VARCHAR(50),
> > > > col8 VARCHAR(50),
> > > > col7 VARCHAR(50)
> > > > )
> > > > GO
>
> > > > BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> > > > WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
>
> > > > As yourdatarow starts with a pipe, that counts as a field.
> > > > Let me know how you get on.
>
> > > > wBob
>
> > > > "brian.tward...@usask.ca" wrote:
> > > > > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > > > > The Format File [_format.txt]
>
> > > > > > 8.0
> > > > > > 9
> > > > > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > > > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > > > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > > > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > > > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > > > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > > > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > > > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > > > > 9 SQLCHAR 0 0 "|" 9 col9 ""
>
> > > > > > TheDataFile [bulk.txt]
>
> > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
>
> > > > > > Error:
>
> > > > > > Event Type: Error
>
> > > > > > Description:
> > > > > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > > > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > > > > >truncated.
> > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > > terminated.
> > > > > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > > > > Length)
> > > > > > at {_somewhere_in_my_code_}
>
> > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > > > > > Initial Query
>
> > > > > > Event Type: Warning
> > > > > > Description:
> > > > > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
>
> > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > > > > I really have no idea why its giving the error. I put a 0 value for
> > > > > the host filedatalength because the MSDN documentation said itwould
> > > > > figure it out on the fly!
>
> > > I've been fighting with this particular beast for days. The errors
> > > seem to change with slight modifications that I don't quite grasp. I
> > > also tend to run into a lot of Unexpected EOF errors as well. If the
> > >datacontains characters above 127 I get this error. I tried the
> > > CODEPAGE = 'ACD' flag, same result. To rule out some cases I'll say
> > > this:
>
> > > 1) Thedatais never over-sized for the table. Thedatacame from the
> > > original table, and is destined to be inside a recreation of that same
> > > table. Hence, anydatagoing into the destination table simply cannot
> > > be too big.
> > > 2) Thedatacan contain 'strange characters' stuff beyond 127. As I
> > > understand it, if I set the CODEPAGE hint in theBULKINSERT
> > > statement, all should be well. It does not help. I've tried to use
> > > unicode files. They are another dimension of pain and suffering that I
> > > will not fully detail here.
> > > 3) For simplicity's sake, any column may be null.
>
> > > Overall,BULKINSERTmay be the worst SQL command I have ever had the
> > > misfortune of trying to use. Yet, here I am, still trying to use it. I
> > > need a programmable solution for moving millions of rows. I looked
> > > into the SQLDMO Transfer object, but it seems to want to move the
> > > entire contents of an object, and I want to be able to limit what I
> > > end up moving around. The DTS wizard isn't an option.
>
> > > I'd rather not dump the stuff into a staging table, because I fear
> > > what that will do to memory consumption and performance of the
> > > destination server. And yes, I've tried dumping into temp tables,
> > > regular precreated tables, all with varchar columns of varying
> > > lengths, some with no max specified at all. All generate an error at
> > > some point.
>
> > > As of the time I am writing this post, I expect my tone to be full
> > > of a-typical usenet bitterness. I'm tired, frustrated, and having been
> > > introduced to the 'definition of insanity', that being to try the same
> > > thing expecting different results, I am beginning to believe that the
> > > code ofBULKINSERTshould be printed on thousand year old parchment,
> > > exoricised, hexed, split up to the four corners of the earth and
> > > ritualistically burned in a pyre fueled only by the most ancient of
> > > trees who may have the will to crush this demonic query.
>
> Reading back it seems that in my attempts to hide any in-housedataI
> sufficiently mangled the query.
> It should have looked something like this:BULKINSERTevent FROM 'event_bulk.txt' WITH (FORMATFILE =
> 'event_format.txt')
>
> The 'bulk' files -- thedatato be imported throughbulkinsert-- are
> generated by my own application upon receiving a dataset remotely. I
> am unaware of how to output the native format ( I believe its
> proprietary ?).
>
> Most rows work with the format file:
>
> 8.0
> 4
> 1 SQLCHAR 0 0 "|" 1 descr RAW
> 2 SQLCHAR 0 0 "|" 2 event_id RAW
> 3 SQLCHAR 0 0 "|" 3 priority RAW
> 4 SQLCHAR 0 0 "~~" 4 servtype_id RAW
> {return line}
>
> But when I reach a row with a char(127+) the import procedure produces
> this error:
>
> System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbetruncated.
>
> I've tried using unicode files, but that doesn't seem to help. In
> fact, it produces -more- of those errors (and yep, I've been sure to
> specify the correct DATAFILETYPE hint). Even if I try the CODEPAGE =
> 'ACP' hint, the error still occurs. :(
>
> Additionally, this error seems to appear where other format files have
> no problem:
>
> System.Runtime.InteropServices.COMException (0x800412DD): [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Could notbulkinsert. Error
> reading destination table column name for source column 18 in format
> file 'C:\Documents and Settings\twarb1\My Documents\Visual Studio
> 2005\Projects\Testing SDR Subscriber Service\Testing SDR Subscriber
> Service\bin\Debug\site_format.txt'.
>
> And here's site_format.txt:
>
> 8.0
> 18
> 1 SQLCHAR 0 0 "|" 1 city_name RAW
> 2 SQLCHAR 0 0 "|" 2 codeword1 RAW
> 3 SQLCHAR 0 0 "|" 3 codeword2 RAW
> 4 SQLCHAR 0 0 "|" 4 corpacct_servco_no RAW
> 5 SQLCHAR 0 0 "|" 5 country_name RAW
> 6 SQLCHAR 0 0 "|" 6 dst_no RAW
> 7 SQLCHAR 0 0 "|" 7 install_servco_no RAW
> 8 SQLCHAR 0 0 "|" 8 phone1 RAW
> 9 SQLCHAR 0 0 "|" 9 phone2 RAW
> 10 SQLCHAR 0 0 "|" 10 servco_no RAW
> 11 SQLCHAR 0 0 "|" 11 site_addr1 RAW
> 12 SQLCHAR 0 0 "|" 12 site_addr2 RAW
> 13 SQLCHAR 0 0 "|" 13 site_name RAW
> 14 SQLCHAR 0 0 "|" 14 site_no RAW
> 15 SQLCHAR 0 0 "|" 15 sitestat_id RAW
> 16 SQLCHAR 0 0 "|" 16 sitetype_id RAW
> 17 SQLCHAR 0 0 "|" 17 state_id RAW
> 18 SQLCHAR 0 0 "~~" 18 zip_code RAW
> {return line}
>
> Now, its the same type of format file... yet I get the error for it
> and not for the various other tables that came before. The
> inconsistancy is what grinds my gears. The error isn't obvious or itwouldappear in every format file, with everydatafile.

Well, some good news. I found out that I had omitted the creation of
the 17th column - state_id - by accident. That error disappeared. Heh.

Still, the truncation error continues :(

brian.twardzik

3/23/2007 4:44:00 PM

On Mar 23, 9:48 am, brian.tward...@usask.ca wrote:
> On Mar 23, 8:02 am, brian.tward...@usask.ca wrote:
>
>
>
> > Bob wrote:
> > > I did notice yourBULKINSERTstatement said FROM '_format.txt' which sounds
> > > like your format file, when it should be thedatafile.
>
> > >BULKINSERTis not so bad. The thing to do is start with one row. Hack a
> > > sample of your datasource down to one row and get that working. This will
> > > help you button down your issues in a more manageable form. Then do 10, 100,
> > > 10,000 etc.
>
> > > If you can get that going, then you know the issue is with thedata, and not
> > > withBULKINSERT. Narrow it down to work out exactly which rows are failing.
> > > Bulkinsertmay give you a line number, or even a column. Open yourdatain
> > > a raw editor and goto that line. Hack it out on its own into a text file.
> > > Make it work.
>
> > > One other alternative, similar to the staging option you mentioned, is to
> > > import alldataas one row, ie put the FIELDTERMINATOR as something not in
> > > the file, like @. Create one table with one very wide column. Don't worry
> > > about memory, just make sure tempdb is nice and big.
>
> > > Then you can use more detailed SQL code to split it out, once you've got it
> > > into the database.
>
> > > Finally, have you tired the native file option when outputting thedata?
> > > You don't need a format file then. That might help.
>
> > > Stick with it! Don't give up.
>
> > > Let me know how you get on.
>
> > > wBob
>
> > > "brian.tward...@usask.ca" wrote:
>
> > > > On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> > > > > I got this to work with your sampledata:
>
> > > > > CREATE TABLE #tmp(
> > > > > col1 VARCHAR(50),
> > > > > col2 VARCHAR(50),
> > > > > col3 VARCHAR(50),
> > > > > col4 VARCHAR(50),
> > > > > col5 VARCHAR(50),
> > > > > col6 VARCHAR(50),
> > > > > col8 VARCHAR(50),
> > > > > col7 VARCHAR(50)
> > > > > )
> > > > > GO
>
> > > > > BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> > > > > WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
>
> > > > > As yourdatarow starts with a pipe, that counts as a field.
> > > > > Let me know how you get on.
>
> > > > > wBob
>
> > > > > "brian.tward...@usask.ca" wrote:
> > > > > > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > > > > > The Format File [_format.txt]
>
> > > > > > > 8.0
> > > > > > > 9
> > > > > > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > > > > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > > > > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > > > > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > > > > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > > > > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > > > > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > > > > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > > > > > 9 SQLCHAR 0 0 "|" 9 col9 ""
>
> > > > > > > TheDataFile [bulk.txt]
>
> > > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
>
> > > > > > > Error:
>
> > > > > > > Event Type: Error
>
> > > > > > > Description:
> > > > > > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > > > > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > > > > > >truncated.
> > > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > > > terminated.
> > > > > > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > > > > > Length)
> > > > > > > at {_somewhere_in_my_code_}
>
> > > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > > > > > > Initial Query
>
> > > > > > > Event Type: Warning
> > > > > > > Description:
> > > > > > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
>
> > > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
>
> > > > > > I really have no idea why its giving the error. I put a 0 value for
> > > > > > the host filedatalength because the MSDN documentation said itwould
> > > > > > figure it out on the fly!
>
> > > > I've been fighting with this particular beast for days. The errors
> > > > seem to change with slight modifications that I don't quite grasp. I
> > > > also tend to run into a lot of Unexpected EOF errors as well. If the
> > > >datacontains characters above 127 I get this error. I tried the
> > > >CODEPAGE= 'ACD' flag, same result. To rule out some cases I'll say
> > > > this:
>
> > > > 1) Thedatais never over-sized for the table. Thedatacame from the
> > > > original table, and is destined to be inside a recreation of that same
> > > > table. Hence, anydatagoing into the destination table simply cannot
> > > > be too big.
> > > > 2) Thedatacan contain 'strange characters' stuff beyond 127. As I
> > > > understand it, if I set theCODEPAGEhint in theBULKINSERT
> > > > statement, all should be well. It does not help. I've tried to use
> > > > unicode files. They are another dimension of pain and suffering that I
> > > > will not fully detail here.
> > > > 3) For simplicity's sake, any column may be null.
>
> > > > Overall,BULKINSERTmay be the worst SQL command I have ever had the
> > > > misfortune of trying to use. Yet, here I am, still trying to use it. I
> > > > need a programmable solution for moving millions of rows. I looked
> > > > into the SQLDMO Transfer object, but it seems to want to move the
> > > > entire contents of an object, and I want to be able to limit what I
> > > > end up moving around. The DTS wizard isn't an option.
>
> > > > I'd rather not dump the stuff into a staging table, because I fear
> > > > what that will do to memory consumption and performance of the
> > > > destination server. And yes, I've tried dumping into temp tables,
> > > > regular precreated tables, all with varchar columns of varying
> > > > lengths, some with no max specified at all. All generate an error at
> > > > some point.
>
> > > > As of the time I am writing this post, I expect my tone to be full
> > > > of a-typical usenet bitterness. I'm tired, frustrated, and having been
> > > > introduced to the 'definition of insanity', that being to try the same
> > > > thing expecting different results, I am beginning to believe that the
> > > > code ofBULKINSERTshould be printed on thousand year old parchment,
> > > > exoricised, hexed, split up to the four corners of the earth and
> > > > ritualistically burned in a pyre fueled only by the most ancient of
> > > > trees who may have the will to crush this demonic query.
>
> > Reading back it seems that in my attempts to hide any in-housedataI
> > sufficiently mangled the query.
> > It should have looked something like this:BULKINSERTevent FROM 'event_bulk.txt' WITH (FORMATFILE =
> > 'event_format.txt')
>
> > The 'bulk' files -- thedatato be imported throughbulkinsert-- are
> > generated by my own application upon receiving a dataset remotely. I
> > am unaware of how to output the native format ( I believe its
> > proprietary ?).
>
> > Most rows work with the format file:
>
> > 8.0
> > 4
> > 1 SQLCHAR 0 0 "|" 1 descr RAW
> > 2 SQLCHAR 0 0 "|" 2 event_id RAW
> > 3 SQLCHAR 0 0 "|" 3 priority RAW
> > 4 SQLCHAR 0 0 "~~" 4 servtype_id RAW
> > {return line}
>
> > But when I reach a row with a char(127+) the import procedure produces
> > this error:
>
> > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbetruncated.
>
> > I've tried using unicode files, but that doesn't seem to help. In
> > fact, it produces -more- of those errors (and yep, I've been sure to
> > specify the correct DATAFILETYPE hint). Even if I try theCODEPAGE=
> > 'ACP' hint, the error still occurs. :(
>
> > Additionally, this error seems to appear where other format files have
> > no problem:
>
> > System.Runtime.InteropServices.COMException (0x800412DD): [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Could notbulkinsert. Error
> > reading destination table column name for source column 18 in format
> > file 'C:\Documents and Settings\twarb1\My Documents\Visual Studio
> > 2005\Projects\Testing SDR Subscriber Service\Testing SDR Subscriber
> > Service\bin\Debug\site_format.txt'.
>
> > And here's site_format.txt:
>
> > 8.0
> > 18
> > 1 SQLCHAR 0 0 "|" 1 city_name RAW
> > 2 SQLCHAR 0 0 "|" 2 codeword1 RAW
> > 3 SQLCHAR 0 0 "|" 3 codeword2 RAW
> > 4 SQLCHAR 0 0 "|" 4 corpacct_servco_no RAW
> > 5 SQLCHAR 0 0 "|" 5 country_name RAW
> > 6 SQLCHAR 0 0 "|" 6 dst_no RAW
> > 7 SQLCHAR 0 0 "|" 7 install_servco_no RAW
> > 8 SQLCHAR 0 0 "|" 8 phone1 RAW
> > 9 SQLCHAR 0 0 "|" 9 phone2 RAW
> > 10 SQLCHAR 0 0 "|" 10 servco_no RAW
> > 11 SQLCHAR 0 0 "|" 11 site_addr1 RAW
> > 12 SQLCHAR 0 0 "|" 12 site_addr2 RAW
> > 13 SQLCHAR 0 0 "|" 13 site_name RAW
> > 14 SQLCHAR 0 0 "|" 14 site_no RAW
> > 15 SQLCHAR 0 0 "|" 15 sitestat_id RAW
> > 16 SQLCHAR 0 0 "|" 16 sitetype_id RAW
> > 17 SQLCHAR 0 0 "|" 17 state_id RAW
> > 18 SQLCHAR 0 0 "~~" 18 zip_code RAW
> > {return line}
>
> > Now, its the same type of format file... yet I get the error for it
> > and not for the various other tables that came before. The
> > inconsistancy is what grinds my gears. The error isn't obvious or itwouldappear in every format file, with everydatafile.
>
> Well, some good news. I found out that I had omitted the creation of
> the 17th column - state_id - by accident. That error disappeared. Heh.
>
> Still, the truncation error continues :(

Victory!
The codepage didn't help, but I figured out what I had been doing
wrong! Using a unicode file worked!
Here's what I did:

StreamWriter bulkText = new StreamWriter(bpath, false,
Encoding.Unicode);

Now, whenever I wrote a line for the file its delim's were normal. A
"|" for the columns and "~\n\r~ for the rows.
However, when I wrote the format file -- still in UTF-8 format, I
needed to use some null characters in order to make unicode characters
in little endian.
ie: "|\0" and "~\\0\\n\\0\\r\\0~\\0" when I used the WriteLine command
on the streamwriter.
The result looked like this:

8.0
4
1 SQLCHAR 0 0 "|\0" 1 descr Latin1_General_CI_AS
2 SQLCHAR 0 0 "|\0" 2 event_id Latin1_General_CI_AS
3 SQLCHAR 0 0 "|\0" 3 priority Latin1_General_CI_AS
4 SQLCHAR 0 0 "~\0\n\0\r\0~\0" 4 servtype_id Latin1_General_CI_AS

btw I switched to the Latin1_General_CI_AS because I was trying to
accomodate for french characters, I'm not certain if it affected the
outcome, but I doubt it.

Thank you wBob for your help!

bob

3/23/2007 5:41:00 PM

Not sure I helped much, but well done on getting there in the end!

"brian.twardzik@usask.ca" wrote:

> On Mar 23, 9:48 am, brian.tward...@usask.ca wrote:
> > On Mar 23, 8:02 am, brian.tward...@usask.ca wrote:
> >
> >
> >
> > > Bob wrote:
> > > > I did notice yourBULKINSERTstatement said FROM '_format.txt' which sounds
> > > > like your format file, when it should be thedatafile.
> >
> > > >BULKINSERTis not so bad. The thing to do is start with one row. Hack a
> > > > sample of your datasource down to one row and get that working. This will
> > > > help you button down your issues in a more manageable form. Then do 10, 100,
> > > > 10,000 etc.
> >
> > > > If you can get that going, then you know the issue is with thedata, and not
> > > > withBULKINSERT. Narrow it down to work out exactly which rows are failing.
> > > > Bulkinsertmay give you a line number, or even a column. Open yourdatain
> > > > a raw editor and goto that line. Hack it out on its own into a text file.
> > > > Make it work.
> >
> > > > One other alternative, similar to the staging option you mentioned, is to
> > > > import alldataas one row, ie put the FIELDTERMINATOR as something not in
> > > > the file, like @. Create one table with one very wide column. Don't worry
> > > > about memory, just make sure tempdb is nice and big.
> >
> > > > Then you can use more detailed SQL code to split it out, once you've got it
> > > > into the database.
> >
> > > > Finally, have you tired the native file option when outputting thedata?
> > > > You don't need a format file then. That might help.
> >
> > > > Stick with it! Don't give up.
> >
> > > > Let me know how you get on.
> >
> > > > wBob
> >
> > > > "brian.tward...@usask.ca" wrote:
> >
> > > > > On Mar 21, 9:55 am, Bob <B...@discussions.microsoft.com> wrote:
> > > > > > I got this to work with your sampledata:
> >
> > > > > > CREATE TABLE #tmp(
> > > > > > col1 VARCHAR(50),
> > > > > > col2 VARCHAR(50),
> > > > > > col3 VARCHAR(50),
> > > > > > col4 VARCHAR(50),
> > > > > > col5 VARCHAR(50),
> > > > > > col6 VARCHAR(50),
> > > > > > col8 VARCHAR(50),
> > > > > > col7 VARCHAR(50)
> > > > > > )
> > > > > > GO
> >
> > > > > > BULKINSERT#tmp FROM 'c:\temp\temp.txt'
> > > > > > WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPNULLS )
> >
> > > > > > As yourdatarow starts with a pipe, that counts as a field.
> > > > > > Let me know how you get on.
> >
> > > > > > wBob
> >
> > > > > > "brian.tward...@usask.ca" wrote:
> > > > > > > On Mar 21, 9:09 am, brian.tward...@usask.ca wrote:
> > > > > > > > The Format File [_format.txt]
> >
> > > > > > > > 8.0
> > > > > > > > 9
> > > > > > > > 1 SQLCHAR 0 0 "|" 1 col1 ""
> > > > > > > > 2 SQLCHAR 0 0 "|" 2 col2 ""
> > > > > > > > 3 SQLINT 0 0 "|" 3 col3 ""
> > > > > > > > 4 SQLCHAR 0 0 "|" 4 col4 ""
> > > > > > > > 5 SQLCHAR 0 0 "|" 5 col5 ""
> > > > > > > > 6 SQLCHAR 0 0 "|" 6 col6 ""
> > > > > > > > 7 SQLCHAR 0 0 "|" 7 col7 ""
> > > > > > > > 8 SQLCHAR 0 0 "|" 8 col8 ""
> > > > > > > > 9 SQLCHAR 0 0 "|" 9 col9 ""
> >
> > > > > > > > TheDataFile [bulk.txt]
> >
> > > > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDD|0987654321|||
> > > > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAA|1234|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAA|123|BB|DDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAAAAAAA|123|BB|DDDDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAAAA|1234|BB|DDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAAAAAAAAAAAAA|1234|BB|DDDDDDDDDDDDDDDDDD|0987654321||CC|
> > > > > > > > |AAAAAAAAAAAA|1234|BB|DDDDDDDDDDD|0987654321||CC|
> >
> > > > > > > > Error:
> >
> > > > > > > > Event Type: Error
> >
> > > > > > > > Description:
> > > > > > > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > > > > > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbe
> > > > > > > >truncated.
> > > > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > > > > terminated.
> > > > > > > > at SQLDMO._Database.ExecuteWithResults(StringCommand, Object
> > > > > > > > Length)
> > > > > > > > at {_somewhere_in_my_code_}
> >
> > > > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> >
> > > > > > > > Initial Query
> >
> > > > > > > > Event Type: Warning
> > > > > > > > Description:
> > > > > > > >BULKINSERT_table_ FROM '_format.txt', KEEPNULLS)
> >
> > > > > > > > For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/....
> >
> > > > > > > I really have no idea why its giving the error. I put a 0 value for
> > > > > > > the host filedatalength because the MSDN documentation said itwould
> > > > > > > figure it out on the fly!
> >
> > > > > I've been fighting with this particular beast for days. The errors
> > > > > seem to change with slight modifications that I don't quite grasp. I
> > > > > also tend to run into a lot of Unexpected EOF errors as well. If the
> > > > >datacontains characters above 127 I get this error. I tried the
> > > > >CODEPAGE= 'ACD' flag, same result. To rule out some cases I'll say
> > > > > this:
> >
> > > > > 1) Thedatais never over-sized for the table. Thedatacame from the
> > > > > original table, and is destined to be inside a recreation of that same
> > > > > table. Hence, anydatagoing into the destination table simply cannot
> > > > > be too big.
> > > > > 2) Thedatacan contain 'strange characters' stuff beyond 127. As I
> > > > > understand it, if I set theCODEPAGEhint in theBULKINSERT
> > > > > statement, all should be well. It does not help. I've tried to use
> > > > > unicode files. They are another dimension of pain and suffering that I
> > > > > will not fully detail here.
> > > > > 3) For simplicity's sake, any column may be null.
> >
> > > > > Overall,BULKINSERTmay be the worst SQL command I have ever had the
> > > > > misfortune of trying to use. Yet, here I am, still trying to use it. I
> > > > > need a programmable solution for moving millions of rows. I looked
> > > > > into the SQLDMO Transfer object, but it seems to want to move the
> > > > > entire contents of an object, and I want to be able to limit what I
> > > > > end up moving around. The DTS wizard isn't an option.
> >
> > > > > I'd rather not dump the stuff into a staging table, because I fear
> > > > > what that will do to memory consumption and performance of the
> > > > > destination server. And yes, I've tried dumping into temp tables,
> > > > > regular precreated tables, all with varchar columns of varying
> > > > > lengths, some with no max specified at all. All generate an error at
> > > > > some point.
> >
> > > > > As of the time I am writing this post, I expect my tone to be full
> > > > > of a-typical usenet bitterness. I'm tired, frustrated, and having been
> > > > > introduced to the 'definition of insanity', that being to try the same
> > > > > thing expecting different results, I am beginning to believe that the
> > > > > code ofBULKINSERTshould be printed on thousand year old parchment,
> > > > > exoricised, hexed, split up to the four corners of the earth and
> > > > > ritualistically burned in a pyre fueled only by the most ancient of
> > > > > trees who may have the will to crush this demonic query.
> >
> > > Reading back it seems that in my attempts to hide any in-housedataI
> > > sufficiently mangled the query.
> > > It should have looked something like this:BULKINSERTevent FROM 'event_bulk.txt' WITH (FORMATFILE =
> > > 'event_format.txt')
> >
> > > The 'bulk' files -- thedatato be imported throughbulkinsert-- are
> > > generated by my own application upon receiving a dataset remotely. I
> > > am unaware of how to output the native format ( I believe its
> > > proprietary ?).
> >
> > > Most rows work with the format file:
> >
> > > 8.0
> > > 4
> > > 1 SQLCHAR 0 0 "|" 1 descr RAW
> > > 2 SQLCHAR 0 0 "|" 2 event_id RAW
> > > 3 SQLCHAR 0 0 "|" 3 priority RAW
> > > 4 SQLCHAR 0 0 "~~" 4 servtype_id RAW
> > > {return line}
> >
> > > But when I reach a row with a char(127+) the import procedure produces
> > > this error:
> >
> > > System.Runtime.InteropServices.COMException (0x80041FD8): [Microsoft]
> > > [ODBC SQL Server Driver][SQL Server]Stringorbinarydatawouldbetruncated.
> >
> > > I've tried using unicode files, but that doesn't seem to help. In
> > > fact, it produces -more- of those errors (and yep, I've been sure to
> > > specify the correct DATAFILETYPE hint). Even if I try theCODEPAGE=
> > > 'ACP' hint, the error still occurs. :(
> >
> > > Additionally, this error seems to appear where other format files have
> > > no problem:
> >
> > > System.Runtime.InteropServices.COMException (0x800412DD): [Microsoft]
> > > [ODBC SQL Server Driver][SQL Server]Could notbulkinsert. Error
> > > reading destination table column name for source column 18 in format
> > > file 'C:\Documents and Settings\twarb1\My Documents\Visual Studio
> > > 2005\Projects\Testing SDR Subscriber Service\Testing SDR Subscriber
> > > Service\bin\Debug\site_format.txt'.
> >
> > > And here's site_format.txt:
> >
> > > 8.0
> > > 18
> > > 1 SQLCHAR 0 0 "|" 1 city_name RAW
> > > 2 SQLCHAR 0 0 "|" 2 codeword1 RAW
> > > 3 SQLCHAR 0 0 "|" 3 codeword2 RAW
> > > 4 SQLCHAR 0 0 "|" 4 corpacct_servco_no RAW
> > > 5 SQLCHAR 0 0 "|" 5 country_name RAW
> > > 6 SQLCHAR 0 0 "|" 6 dst_no RAW
> > > 7 SQLCHAR 0 0 "|" 7 install_servco_no RAW
> > > 8 SQLCHAR 0 0 "|" 8 phone1 RAW
> > > 9 SQLCHAR 0 0 "|" 9 phone2 RAW
> > > 10 SQLCHAR 0 0 "|" 10 servco_no RAW
> > > 11 SQLCHAR 0 0 "|" 11 site_addr1 RAW
> > > 12 SQLCHAR 0 0 "|" 12 site_addr2 RAW
> > > 13 SQLCHAR 0 0 "|" 13 site_name RAW
> > > 14 SQLCHAR 0 0 "|" 14 site_no RAW
> > > 15 SQLCHAR 0 0 "|" 15 sitestat_id RAW
> > > 16 SQLCHAR 0 0 "|" 16 sitetype_id RAW
> > > 17 SQLCHAR 0 0 "|" 17 state_id RAW
> > > 18 SQLCHAR 0 0 "~~" 18 zip_code RAW
> > > {return line}
> >
> > > Now, its the same type of format file... yet I get the error for it
> > > and not for the various other tables that came before. The
> > > inconsistancy is what grinds my gears. The error isn't obvious or itwouldappear in every format file, with everydatafile.
> >
> > Well, some good news. I found out that I had omitted the creation of
> > the 17th column - state_id - by accident. That error disappeared. Heh.
> >
> > Still, the truncation error continues :(
>
> Victory!
> The codepage didn't help, but I figured out what I had been doing
> wrong! Using a unicode file worked!
> Here's what I did:
>
> StreamWriter bulkText = new StreamWriter(bpath, false,
> Encoding.Unicode);
>
> Now, whenever I wrote a line for the file its delim's were normal. A
> "|" for the columns and "~\n\r~ for the rows.
> However, when I wrote the format file -- still in UTF-8 format, I
> needed to use some null characters in order to make unicode characters
> in little endian.
> ie: "|\0" and "~\\0\\n\\0\\r\\0~\\0" when I used the WriteLine command
> on the streamwriter.
> The result looked like this:
>
> 8.0
> 4
> 1 SQLCHAR 0 0 "|\0" 1 descr Latin1_General_CI_AS
> 2 SQLCHAR 0 0 "|\0" 2 event_id Latin1_General_CI_AS
> 3 SQLCHAR 0 0 "|\0" 3 priority Latin1_General_CI_AS
> 4 SQLCHAR 0 0 "~\0\n\0\r\0~\0" 4 servtype_id Latin1_General_CI_AS
>
> btw I switched to the Latin1_General_CI_AS because I was trying to
> accomodate for french characters, I'm not certain if it affected the
> outcome, but I doubt it.
>
> Thank you wBob for your help!
>
>

microsoft.public.sqlserver.programming

BULK INSERT, "String or binary data would be truncated."

brian.twardzik

brian.twardzik

bob

Robert Klemme

brian.twardzik

bob

brian.twardzik

brian.twardzik

brian.twardzik

bob

x Login to ForumsZone