October 13, 2005 at 4:15 pm
I'm writing data to an excel spreadsheet from SQL2000 DTS package. I've read plenty about this problem moving data from excel into a table. I've changed the excel guessing in the registry on both client & server. Same problem & I've got large text fields to put in excel.
Anyone run into this, I NEED A FIX!
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
October 13, 2005 at 4:32 pm
how big are your textfields?
can you post your spreadsheet? I would love to give it a try.
October 14, 2005 at 8:19 am
Theres nothing special about the spreadsheet...I'm only writing 8 columns to it...and so far I've run into a wall with this problem trying to move very little text. One paragraph worked but two and it failed!
My table is made of: int, datetime, int, int, nvarchar(50), int, datetime, ntext.
I also replaced ntext with nvarchar(4000), it made no difference.
Also tried changing the column in the spreadsheet to different types....made no difference...
Thx
UPDATE!
I thought it might be my asp but....
I opened the DTS package and started the data pump from there....no good.
I also moved the ntext column to the first position in the table...no good!
It is writing some text to the spreadsheet...looks like 255 characters worth...
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
October 15, 2005 at 4:09 am
Hi,
What version of Sql Server do u have installed on server.
This problem is fixed in SP4.
from
Killer
October 15, 2005 at 10:06 am
I found the problem...and I think its of the gotcha type...maybe a bug.
When I setup the connections in DTS, SQLserver said "Oh I have a big text column here so when I create the table on the worksheet, I'll set it to LongText!" Well guess what...theres no LongText data type in excel! So excel set it to general not knowing what LongText was. Once I set the create table in excel to "Text" everything worked perfectly! In my opinion, MS SQLserver should already know that MS Excel doesn't have a LongText type!
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
August 22, 2006 at 11:47 pm
I got the same problem. but i got the solution like following way.
I have 10 columns & 200 rows in excel worksheet. Out of 10 columns 1 column has some rows which size is too big in size. so I got the same error message "data for source column 8 is too large for the specified buffer size".
Solutions: I cut & paste the row (which has big column value) as first row of the excel worksheet. and its works fine. everyone thinks why? and how it works fine? coz excel grab first 8 rows and decide the buffer size for each column. if your row (which has max or big value as a column) didnt come in the first 8 rows then excel provide all columns buffer size as per first 8 rows, it reads.
Try out this & if not works let me know.
Cheers !!!!
August 25, 2007 at 7:02 am
just as you I receive the same error.
The solution of Nizarbhai is not possible for me because I have 100 excel sheets. And I was not planning to open each excel sheet. but it works for you.
I'm using DTS to import my Excel files in A loop, as a matter of fact you can see an example of the DTS i'm using www.sqldts.com/246.aspx
I also used the microsoft solution but nothing happens.
I do not understand what rocker333 meant by : "Once I set the create table in excel to "Text" everything worked perfectly"
Are you doing this in a dts?
Or those some body else have a solution for me
please advise me
November 13, 2007 at 7:27 am
I've had a similar problem, but my data got bigger as the file went on making it difficult to manage these in the first 8 rows.
As a result I've added an additional row of data with 512 random characters (I originally tried 2048 but this caused it to be ignored during the DTS process) in each column at the top of the sheet (row 2). Once this was done it imported fine and then I removed the added row from the table on the SQL Server.
Microsoft has some information on this at http://support.microsoft.com/kb/281517/EN-US/
November 14, 2007 at 7:04 am
By default, DTS samples the first 8 rows to determine the column size. If you use the Disconnected Edit, you can tell it what the column sizes will be and it won't do the sampling.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
January 29, 2009 at 10:53 am
A year later... 🙂
The following fixed it in my case. Change the reg entry TypeGuessRows to 0
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"win32"="C:\\WINDOWS\\system32\\msexcl40.dll"
"DisabledExtensions"="!xls"
"ImportMixedTypes"="Text"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"TypeGuessRows"=dword:00000000
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply