binary_checksum and rowsize error?

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(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D 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.id)
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(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 o.name
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D 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(binary_checksum(=ADm.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id =3D o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x=20
Regards=20
LiamIt works at my database. The cause is that there is a row in syscomments
that has a rather long value in the text column, and that pushes the rowsize
of an intermediate result set over the maximum row size. SQL Server uses
only an estimate from the statistics to determine how large the largest row
can be in any intermediate result sets, so it can create an execution plan
that might fail with very long rows, especially when the very long rows are
rare. You can avoid this by using OPTION(ROBUST PLAN).
Jacco Schalkwijk
SQL Server MVP
<liam.caffrey@.gmail.com> wrote in message
news:1117784150.912579.240300@.g14g2000cwa.googlegroups.com...
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(binary_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.id)
,checksum_agg(binary_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.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 o.name
,checksum_agg(binary_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(binary_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