February 13, 2003 at 9:23 am
In some of my software that processes all tables in a customer's database i get this error
Cannot sort a row of size 8144, which is greater than the allowable maximum of 8094.
SQL Server will allow you to create such tables but if you try to do anything with them (select, insert etc) they kick an error.
The trouble is that they interfere with my processing so i need a script to find all such tables and notify user or skip them.
Has anyone bumped into this problem.
This is a good example of the type of analysis I'd like to add to SQLClean - where the product scanned for such problems.
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
February 13, 2003 at 10:12 am
select obj.name as TableName, sum(col.length) as RecordLength
from syscolumns col
inner join sysobjects obj
on obj.xtype = 'U'
and obj.id = col.id
group by obj.name
having sum(col.length) > 8094
order by obj.name
February 13, 2003 at 10:22 am
I also think you need to add to that sum, the size of the null block, and the variable block. Null block contains the null bit map, and the variable block has offsets to where each variable length field begins.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 13, 2003 at 10:32 am
thanks for the posts. we'll look into implementing this check.
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
February 20, 2003 at 8:16 am
Can any one tell me the general philosophy on these tables - are they bad in all cases, are they acceptable in others?
These tables seem to work fine until the data in them begins to fill and get over the limit. So it seems that the creation and use of such tables, in an of themselves, will not result directly in errors but that over time, based on the amount of data contained, errors could suddenly appear.
My problem is that my script fails when I try to process databases that contain such tables but is it my commercial software's problem - or is it the customers problem with the "bad" table.
Either way we will probably integrate the above script to ignore such tables or flag them as "unprocessed".
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
February 20, 2003 at 9:02 am
I'd say its bad, but you know you'll hit cases where it exists, so not much choice except to use the code you're discussing.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply