Stored procedures over 32K always show as changed

Keith_Walton

Whenever one of my procedures gets really large and exceeds 32K, it always shows up as being different when I try to synchronize it between databases. In the past, I worked around this by splitting them into multiple procs - but this just makes the maintenance harder for what I'm doing. Is this is a known bug or limitation? Is there a workaround?

This is how I determine the size:

SELECT
Object.[Name] AS [Name],
sum (len(Comments.[Text])) AS Definition

FROM
sysobjects AS Object

INNER JOIN
syscomments AS Comments
ON Object.[ID] = Comments.[ID]

WHERE
--permission
PERMISSIONS(Object.[ID]) != 0 --has permission
--xtype
AND Object.xtype IN ('P','FN','TF', 'IF') --routines

GROUP BY
Object.[Name]

ORDER BY
sum (len(Comments.[Text])) desc[/b]

Chris Auckland

Thanks for your post.

I haven't encountered this kind of limitation before.

Does this mean that the large sp's still state that they're different after synchronization?

I tested this by inserting massive comment blocks into one of my sp's. The behaviour in SQL Compare remained the same when (using your query) the definition > 40000.

What differences are being reported when the sp's get over 32K?

Keith_Walton

> Does this mean that the large sp's still state that they're different after synchronization?

Yes.

> What differences are being reported when the sp's get over 32K?

I think it just highlights the last line procedure, but there is no visible difference. I'll check with my boss who does the synch.

Keith_Walton

Nothing is highlighted as different between the two procs.

Keith_Walton

Is there a debug mode or something that will create a log file? My boss is going to make me split the procs again... this is getting out of hand.