Handy Script for Checking Breakages in SQL Server Objects (Views, Stored Procedures etc …)


Today i was working with some column removal and there were lot of stored procedures that i had to verify if they are fine or not.

So, wrote the following script to check each and every stored procedure for any breakages.

Copy and Execute on your database and enjoy!!!


DECLARE @spName nvarchar(1000)

DECLARE product_cursor CURSOR FOR

SELECT CONVERT(nvarchar(20), sch.name) + ‘.’ +  CONVERT(nvarchar(500), obj.Name)

FROM sys.sql_modules modu

INNER JOIN sys.objects obj

INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id

ON modu.object_id = obj.object_id

WHERE obj.type = ‘P’ — for stored prcedures… 

OPEN product_cursor

FETCH NEXT FROM product_cursor INTO @spName



PRINT @spName

exec sp_refreshsqlmodule @spName

FETCH NEXT FROM product_cursor INTO @spName


CLOSE product_cursor

DEALLOCATE product_cursor


Issue: Length of LOB data to be replicated exceeds configured maximum 65536


Length of LOB data (78862) to be replicated exceeds configured maximum 65536


We published some articles that use varchar(max) and a lot of XML data types for the columns. When we enabled replication, we got the error Length of LOB data (78862) to be replicated exceeds configured maximum 65536


Increase the size that can be replicated. This is applicable for transactional replication only.

T-SQL:  EXEC sp_configure ‘max text repl size’, 2147483647

SSMS (excerpt from BOL):

    1. In Object Explorer, right-click a server and select Properties.
    2. Click the Advanced node.
    3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.


BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3056cf64-621d-4996-9162-3913f6bc6d5b.htm

Taken from: http://sqlmusings.wordpress.com/2008/09/22/issue-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536/