Andy Davies <> wrote:
> Hi, we used to hear a lot about people migrating to SQL Server 'cos
> they'd hit Fox's 2Gb limit - I've just hit a much lower limit in SQL:
>
> A user for a system I'm prototyping has set up a lot of data in Excel
> - I have tried everything to import this to SQL without success:
> I keep getting a "data has exceeded buffer size" error. The trouble
> is that the user has 'cut and paste'd huge chunks of data into some
> Excel cells (well over the agreed design limits but I was hoping to
> trim it later). I tried setting the target table columns to 4095 or
> 8000 but then I get warnings that I will likely exceed the maximum
> rowsize of 8000 (doesn't seem very big! - just 4 x 2kb fields) So in
> desperation I tried setting the target table columns to 'text' - same
> problem. I can sidestep the problem by using Excel to convert to .csv
> or dBaseIII but this limits all columns to 257 (?)
As text for one answer.
Now how about using access as the vehicle to get the data from to?
In the past you had to play with the DTS package:
1) Open up your saved DTS package in the designer.
2) Right click on an empty spot in the designer window and choose
"Disconnected Edit".
3) Expand the connection for each Excel connection and under OLE DB
Properties choose "Extended Properties".
4) Edit the string "value" and add ;IMEX=1 to the connection properties.
It'll look something like "Excel 8.0;HDR=YES;IMEX=1"
5) Save your package and rerun it.
Good luck.
Stephen Russell
DBA / .Net Developer
Memphis TN 38115
901.246-0159
"A good way to judge people is by observing how they treat those who
can do them absolutely no good." ---Unknown
http://spaces.msn.com/members/srussell/
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.13/726 - Release Date: 3/18/2007
3:34 PM
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
