"SQL noob" <SQL n***@discussions.microsoft.com> wrote in message
news:DDD76D11-40D4-489F-8A63-7B873CAE6E25@microsoft.com...
> we are using microsoft SQL 2005 server, we are trying to put data.txt file
> into the server, the file we put in have been edited below, the three
names
> of the column are written in the file. what we should use for the data
type
> of the table?
>  the following link is the data we have been edited, pls open up with the
> microsoft excel. The first two rows is to show the column numbers and the
> names of the column. The name of the table is called av.The first two rows
is
> not the data we are just added in for easy understanding. i am suspecting
the
> mistake is in our table as the data is fixed.
>
>
> http://www.bigupload.com/d=28C9325A
>
> i am out of my wits to thing for what data type to use. because for
> everything i tried i got the error below  i appreciate a lot if someone
can
> help me .
> This is the table we have done.before using this bcp in command prompt
>
> CREATE TABLE av(Omim_No int NULL,
>                     No_of_av decimal NULL,
>                    Description text NULL)
>
> C:\Disease_Database_TongBoon_Dec2005_March2006\Results>bcp OMIM.dbo.av in
> data.txt -c -T
>
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
>
> BCP copy in failed
>
>

"Erland Sommarskog" wrote:

> [Reposted, as posts from outside msnews.microsoft.com does not seem to make
> it in.]
>
> SQL noob (SQL n***@discussions.microsoft.com) writes:
> > we are using microsoft SQL 2005 server, we are trying to put data.txt file
> > into the server, the file we put in have been edited below, the three
> > names of the column are written in the file. what we should use for the
> > data type of the table?
> >  the following link is the data we have been edited, pls open up with the
> > microsoft excel. The first two rows is to show the column numbers and
> > the names of the column. The name of the table is called av.The first
> > two rows is not the data we are just added in for easy understanding. i
> > am suspecting the mistake is in our table as the data is fixed.
>
> You have it a bug in SQL 2005, and I've filed
> http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK47816
> for it.
>
> I tested this in the CTP of Service Pack1, but the bug is still there. I'm
> sort of amazed that this made it through the beta programme. Unless there
> is something special with your data file, that I did not dig into.
>
> Anyway, if you have SQL 2000 around, you can use the BCP that comes
> with SQL 2000. I was able to load the file successfully to SQL 2005 with
> that BCP. Almost. Two things:
>
> 1) Specify -F3 to skip the two header rows.
> 2) There are three entries in the file that has embedded newlines, so
>    you need to find out a way how to deal with this.
>
> Another alternative is to load the file with BULK INSERT:
>
>    bulk insert av FROM 'C:\temp\data.txt' with (firstrow = 3)
>
> Note that the file location here is on the machine where SQL Server
> is running.
>
> If none of the workarounds are acceptable to you, and if you feel that
> you need a hotfix, you would have to open a case with Microsoft to get
> there.
>
> Finally: since you are on SQL 2005, you should consider using the nex data
> type varchar(MAX) rather than "text". varchar(MAX) is a first-c class
> citizen that works very similar to regular varchar, in differece to text
> which is difficult to work with.
>
> Howeever, it doesn't help to change to varchar(MAX) for your BCP problem.
> The bug affects this data type as well.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@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
>