SQL Server, 对于指定的缓冲区大小而言,源列的数据太大

当从Excel导入数据到Sql Server中,可能会出现以下问题:


SQL Server从 Excel表中前几行数据判断数据类型和长度,因此前几行的数据写得短些,以后导入不会出现问题。



I have a problem of importing excel data into the SQL database. When I import the database (finish all importing steps), it tell me "Fail to copy 1 table" and I go to view the error message say that "Error at Source for Row number 19. Errors encountered so fat in the task: 1. Data for source column 2 ('Notes') is too large for the specified buffer size."

I try to remove that line record but still have many lines have the same problem. So, can I change the importing buffer size in SQL Server to make me import data becomes successful??

Thanks a lot.
you're probably loading it to a pre-made table right? I'm assuming you have a varchar field that isn't large enough. Try maxing it out to 8000 to make sure. It's kind of a pain to load in batch. It always makes me nervous
Thanks for reply. I try it but I got the same error message. Is it need to set more bigger buffer size for SQL server importing the data?

Oh... I got it.
Because the SQL will specify the buffer size on that column field of the first eight rows. So, I moved the field which have many character to the second row. Then it works.
Thanks for help :)

Data for source column is too large for the specified buffer size...

Hello there,

I have and small excel file, which when I try to import into SQl
Server will give an error "Data for source column 4 is too large for
the specified buffer size"

I have four columns in the excel file, one of the column contains a
large chunk of data so I created a table in SQL Server and changed the
type of the field to text so I could accomodate this field but still
no luck.

Any suggestions as to how to go about this.

Thanks in advance,
Srikanth pai

How are you importing? bcp, dts or what?
We are currently using bcp to import text fields without any problems.
I have used also used dts for importing text fields in the past.

Hello there,

I am using DTS to import the excel file...

Srikanth Pai


Some suggestions for you.

(1) Try using bcp.
(2) Try with a manually created file and see if you can work out the
differences between your sample file and the prblem file. It maybe
some data, eg quotes or control chars that are
(3) Send me email containing your schema and a sample data file
(cutdown to the 2meg hotmail limit) and I'll have a look.
Thanks promised one for everything, what I did here was saved the
excel file as a .txt file and then imported the file sucessfully, I
have never tried bcp?

FYI all, I had problems exporting to SQL from Access. So by exporting
to text (from access) and then to SQL using DTS...it worked!

posted @ 2007-08-28 21:58 emanlee 阅读(...) 评论(...) 编辑 收藏