checksum_agg and row size error. Require explanation.

[Liam Caffrey]

Hi,

I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?

-- Works OK
select o.id
,checksum_agg(b inary_checksum( m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P', 'TF','TR','V')
group by o.id

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.i d)
,checksum_agg(b inary_checksum( m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P', 'TF','TR','V')
group by object_name(o.i d)

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(b inary_checksum( m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P', 'TF','TR','V')
group by o.name

-- Workaround
select getdate()
,object_name(x. id)
,check_sum
from (select m.id
,checksum_agg(b inary_checksum( m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P', 'TF','TR','V')
group by m.id) as x

Regards

Liam

[Razvan Socol]

Use "OPTION (ROBUST PLAN)":

select o.name
,checksum_agg(b inary_checksum( *m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P', 'TF','TR','V')
group by o.name
OPTION (ROBUST PLAN)

This forces SQL Server to use a plan that works for the maximum
potential row size.

Razvan