How can we help you today? How can we help you today?

Stored procedures over 32K always show as changed

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]
Keith_Walton
0

Comments

4 comments

Sort by
  • 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?
    Chris Auckland
    0
  • 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
    0
  • Keith_Walton
    Nothing is highlighted as different between the two procs.
    Keith_Walton
    0
  • 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.
    Keith_Walton
    0

Add comment

Please sign in to leave a comment.