Options
Stored procedures over 32K always show as changed
Keith_Walton
Posts: 24
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]
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]
Comments
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?
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.
Chief Software Architect
NHXS
Chief Software Architect
NHXS
Chief Software Architect
NHXS