|
|
Posted by panic attack on 08/08/06 08:33
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.ted.byers@rogers.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;2004-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, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|