Cannot sort a row of size...

GaryHampson

Hello Brian,

I have been getting this error when I run SQL Compare from the command line:

Error: Error occurred connecting to database: Cannot sort a row of size 8108,
which is greater than the allowable maximum of 8094.

I then run the following to see if I can determine what table is causing the error:

select	object_name(id) as objectname,
			sum(length) LenSum 
from  	 syscolumns 
where 	left(object_name(id),1) in ('t','z')  --perm and temp table naming convention
group by object_name(id) 
order by 2 desc

The results that are returned show no table with a rowsize of more than 5193. Any ideas as to a resolution?

Thanks..

Peace,
Gary Hampson

Daniel Handley

Hi Gary

The problem is probably due to the text in row value being larger than the page size. If you don't have any entries who’s length is greater than 5193 you will still receive the error.

Here is a statement to execute on the database to check the text in row value.

exec sp_MsForEachTable "SELECT OBJECTPROPERTY(object_id('?'),'TableTextInRowLimit') AS '?'"

Once you have found the problem you can use the store procedure sp_tableoption and change the option value 'text in row' to less than 8108.

Regards
Dan

GaryHampson

Daniel Handley wrote:

Hi Gary

The problem is probably due to the text in row value being larger than the page size. If you don't have any entries who’s length is greater than 5193 you will still receive the error.

Here is a statement to execute on the database to check the text in row value.

exec sp_MsForEachTable "SELECT OBJECTPROPERTY(object_id('?'),'TableTextInRowLimit') AS '?'"

Once you have found the problem you can use the store procedure sp_tableoption and change the option value 'text in row' to less than 8108.

Regards
Dan

I have run the SP and I have no tables where that property has been set. (all return 0). Any other ideas?

Peace,
Gary Hampson

Daniel Handley

Hi Gary

Can you let us know exactly the steps you are performing to get the error, and when it occurs.

It may be worth running SQL Profiler to see what command are being sent to SQL when the error occurs.

Can you send the results to support@red-gate.com

Regards
Dan