Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waterduck
Aged Yak Warrior
982 Posts |
 Posted - 2011-05-19 : 00:22:18
|
| Hi all,I am extracting data from DB2, however...it consist of 800++ field...at row 748, it actually hit this error. Anyway to outcome this?Thx alot!!!Best RegardsWaterDuck |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-05-19 : 04:41:12
|
| You are not being limited by the number of fields (max 1024), it is possibly bytes per row (8,060 bytes) or combined size of fields in index (900 bytes). Look at the field types and field sizes, e.g are you using char when field is often empty - then use varchar, can numeric fields use smaller types e.g. would decimal(10,2) suit instead of decimal(18,2), or smallint instead of int.http://msdn.microsoft.com/en-us/library/ms187745.aspxhttp://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-19 : 05:51:36
|
| Are you using v2008? The rowsize is still 8060 but it can overflow now and move large columns off row.The sum of fixed length column sizes must still fall within the limit though - but I would expect that to have failed on the first row.Are you using sparse columns?What's the full error message.Also look at the source data around this row to see if there's any issues with it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2011-05-19 : 21:24:36
|
| i end up create 2 table and use view to combine them =x*im not allow to change table structure, no varchar(max), no text, no char, jus lots of decimal and varchar |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-05-20 : 13:13:23
|
quote:
Originally posted by lappin
You are not being limited by the number of fieldscolumns (max 1024)<snip>
A little off topic, but...It is true that you cannot have more than 1024 non sparse columns. However, as an FYI, you can have up to 30,000 columns if you use a wide table. Not may people know about wide tables and, probably, rightfully so.quote:
A wide table is a table that has defined a column set. Wide tables use sparse columns to increase the total of columns that a table can have to 30,000. The number of indexes and statistics is also increased to 1,000 and 30,000, respectively. The maximum size of a wide table row is 8,019 bytes. Therefore, most of the data in any particular row should be NULL. To create or change a table into a wide table, you add a column set to the table definition. The maximum number of nonsparse columns plus computed columns in a wide table remains 1,024.
See WIDE TABLES:http://msdn.microsoft.com/en-us/library/ms186986.aspx |
|
|
|
|
|
|
|
|
Cannot create a row of size greater than the allow