On to Bulk Insert issues

[Ted]

OK, I tried this:

USE Alert_db;

BULK INSERT funds FROM 'C:\\data\\myDa ta.dat'
WITH (FIELDTERMINATO R='\t',
KEEPNULLS,
ROWTERMINATOR=' \r\n');
And I got the following errors.
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classe s_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
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 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".
One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

I do not know what "State 1" vs "State 8" is supposed to mean.

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I've read bcp
should be useful for this, but I have yet to figure that out.

In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.

BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.

Thanks

Ted

[Erland Sommarskog]

Ted (r.*********@ro gers.com) writes:

One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

That's correct, if optionally means just optionally, so that you
could have:

9;Some unquoted data;12;9.234;2 004-12-12
19;"Some quoted data";-12;31.4;2003-02-23

But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:

8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""

The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.

First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.

The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.

Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.

Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.

I do not know what "State 1" vs "State 8" is supposed to mean.

You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[panic attack]

hi all again...
it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...

well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"

and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?

what should i do to get over this problem?

thanks a lot

Tunc Ovacik

*************** *************** *************** *************** *******

Erland Sommarskog wrote:

Ted (r.*********@ro gers.com) writes:

One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

That's correct, if optionally means just optionally, so that you
could have:

9;Some unquoted data;12;9.234;2 004-12-12
19;"Some quoted data";-12;31.4;2003-02-23

But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:

8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""

The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.

First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.

The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.

Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.

Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.

I do not know what "State 1" vs "State 8" is supposed to mean.

You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

panic attack (tu*********@gm ail.com) writes:

it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...

well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"

and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?

what should i do to get over this problem?

First start a new thread, so we can keep different problems apart.

You can try the -e option to get errors to a file, you will then see which
records in the file that provokes this error. I need however add the caveat
that not all errors get listed in the error file, and I don't remember if
this error gets lists.

The two most plausible reasons for the error is
1) the file has data that does not fit the table columns.
2) there is an error with your delimiters, so that BCP gets out of sync.

If that does not help, post the CREATE TABLE command for the table and the
exact command line for BCP you are using. If you use a format file, please
also include a format file. Finally, include a sample of the input file.
Best is if the sample produces the error message. If the input file
exceeds 80 characters in length include it as an attachment, so it does
not get wrecked in news transport.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ted]

Thanks Erland,

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

1) With one file, some of which goes into one tabe and some goes into
another, the last column contains null values, and consequently the
load fails. The key error seems to be:

Error 0xc020901c: Data Flow Task: There was an error with input column
"Dist_Unit" (67) on input "Destinatio n Input" (51). The column status
returned was: "The value could not be converted because of a potential
loss of data.".
(SQL Server Import and Export Wizard)

Dist_Unit is the last column and more often than not contains null
values.

This is especially puzzling since the wizard, when asked to show a
preview, properly displays the data without complaint.

2) With a different data file/table, there are two fields containing
integers, both of which could contain nulls, and while the wizard will
import the data without complaint, it silently converts the nulls to
zero. Is there a way to tell the wizard to keep the nulls as nulls?
If so, might this fix the problem in item #1?

3) I am trying to populate a lookup table from data used in item #1.
Of course, in that file, there will be multiple occurances of most
supplier code/supplier name pairs (one for each product supplied by the
supplier). This leads to the wizard complaining about violating the
primary key. Is there a way to tell the wizard to ignore duplicate
records?

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

Thanks

Ted

[Ted]

BTW: I examined the problematic data files using Open Office's Writer,
configured to show non-printable characters, and invariably the number
of fields is correct, with the right number of tabs.

Ted

[Ted]

I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero. This represents a major distortion of
the meaning of the column. For this column, zero carries a very
different meaning from null. Worse, since the column had many records
in which the value was zero, it is not possible after the insert to
recover the nulls!

How can I tell the data import wizard to preserve my nulls?

Ted

[Erland Sommarskog]

Ted (r.*********@ro gers.com) writes:

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

Wait a minute, last night you were using BULK INSERT, now you are using
the Import Wizard which uses SQL Integration Services that I know next
to nothing about. So I cannot assist with that part.

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

As I understand it, Mgmt Studio does not offer any interface to
Integration Services. To this end you should use Business Intelligence
Development Studio.

I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero.

Just a stupid check: you don't happen to have a default of 0 on those
columns.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ted]

Erland Sommarskog wrote:

Ted (r.*********@ro gers.com) writes:

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

Wait a minute, last night you were using BULK INSERT, now you are using
the Import Wizard which uses SQL Integration Services that I know next
to nothing about. So I cannot assist with that part.

Actually, I have been experimenting with BULK INSERT, bcp, and the
import wizard simultaneously. I find some inconsistency in how they
work. With bulk insert, it seems I can tell it to preserve my nulls,
and I haven't found out how to do that with the wizard (which is
accessable in SQL Server Management Studio by selecting the database
and then, from the popup menu selecting either import data or export
data).

OTOH, the wizard lets me specify in the second dialog that the text
fields are enclosed by quotes while it seems I may be only able to do
that by creating a format file for use by bcp or bulk insert.

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

As I understand it, Mgmt Studio does not offer any interface to
Integration Services. To this end you should use Business Intelligence
Development Studio.

It is accessable in Mgmt Studio through the popup menu accessible on
each database on the server.

I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero.

Just a stupid check: you don't happen to have a default of 0 on those
columns.

No. On that particular table, there are no default values.

Thanks

Ted

[panic attack]

many thanks...
my problem has been solved.

tunc

Erland Sommarskog wrote:

panic attack (tu*********@gm ail.com) writes:

it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...

well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"

and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?

what should i do to get over this problem?

First start a new thread, so we can keep different problems apart.

You can try the -e option to get errors to a file, you will then see which
records in the file that provokes this error. I need however add the caveat
that not all errors get listed in the error file, and I don't remember if
this error gets lists.

The two most plausible reasons for the error is
1) the file has data that does not fit the table columns.
2) there is an error with your delimiters, so that BCP gets out of sync.

If that does not help, post the CREATE TABLE command for the table and the
exact command line for BCP you are using. If you use a format file, please
also include a format file. Finally, include a sample of the input file.
Best is if the sample produces the error message. If the input file
exceeds 80 characters in length include it as an attachment, so it does
not get wrecked in news transport.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx