Jeremy,
Thank you very much.
I have found a way to solve the problem. It is DTS problem. DTS checks
the column size for the first 8 rows before transfer to decide the
destination column size. If there are some data over the size in the
rest of rows, it will give you the error message like one I saw.

Solution: I insert a new row at the first row in the excel spread sheet
and insert a string with big length to the description column. Then the
problem is solved.

Thanks!

-----Original Message-----
From: Jeremy Greaves
[mailto:mssqldba-ezmlmshield-x48539822.[Email address protected]
Sent: Friday, September 28, 2007 10:00 AM
To: LazyDBA Discussion
Subject: RE: Problem when transfer data from excel to sql database


Have you checked all the data down that column, it's possible that at
least one cell has a size greater than 3000.

It might be that a particular record has a "null character" at the end
of its contents, this sometimes does wierd things to the size of the
data, and may well extend beyond the end of what is expected, even
though it claims to be no more than 3000.

You could always try the "kludge method", and extend the length of your
column until the data fits... then at least you'd know;
A) what the "true" length of the data is
B) which record it is that is causing the problem.

Or...
In the DTS create an ActiveX script that physically limits the contents
of your incoming data to the required length of 3000 for that particular
field.
e.g. DTSDestination("<TableFieldName>") =
substring(DTSSource("<IncomingFieldName>"),1,3000)


Thanks,

Jeremy Greaves
Fenris Software Consulting, Inc


>From: "Fangjing Wang - SAS(IT) "
><mssqldba-ezmlmshield-x10082149.[Email address protected]
>To: "LazyDBA Discussion" <[Email address protected]
>Subject: Problem when transfer data from excel to sql database
>Date: Fri, 28 Sep 2007 09:35:17 -0500
>
>Hi Everyone,
>I am trying to transfer data from a .xls file to Sql Database using
DTS.
>During the transfer, there is an error message popup. It said "Error
>Description: Data for data source column 3 (Description) is too large
>for specified buffer size.". I checked the column 3, the length of
>content on this column is not too long. The size of corresponding
>column on the SQL database is 3000 varchar. I think that it is enough
>for that column. Do you know the real problem?
>
>Thanks!
>
>--Fangjing
>
>"Samsung Austin Semiconductor" made the following annotations.
>-----------------------------------------------------------------------
>-------
> NOTICE : This email message is for the sole use of the intended
>recipient(s) and may contain confidential and /or privileged
information.
>Any unauthorized review, use, disclosure or distribution is prohibited.

>If you are not the intended recipient, please contact the sender by
>reply email and destroy all copies, including without limitation
>electronic or printed versions, of this message and any attachments .
>=======================================================================
>=======
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
>a dba job: http://jobs.lazydba.com To subscribe :
>http://www.LazyDBA.com To unsubscribe:
>http://www.lazydba.com/unsubscribe.html
>




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



"Samsung Austin Semiconductor" made the following annotations.
------------------------------------------------------------------------------
NOTICE : This email message is for the sole use of the intended recipient(s) and may contain confidential and /or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies, including without limitation electronic or printed versions, of this message and any attachments .
==============================================================================


MS Sql Server LazyDBA home page