Tips and Tricks for:  SQL Server, Replication

Problem: Length of text, ntext, or image data (x) to be replicated exceeds configured maximum 65536.

This message occurs when you attempt to insert into a text, ntext, or image column that is published in a replication article.

Solution: Use sp_configure to increase 'max text repl size'

The default value for the maximum configuration size is only 65536.  Once it's increased, the insert can proceed.  To increate the size execue sp_configure on 'max text repl size'.  This stored procedure does the job:

CREATE PROC usp_CONFIGURE_ReplicationSizeForBlobs 

   @NewSize int = 100000000

/*
* Sets the 'max text repl size' instance wide configuration setting
* that governs the maximum size of an image, text, or ntext column
* in a replicated table.
*
* Example:
exec usp_CONFIGURE_ReplicationSizeForBlobs default
**********************************************************************/
AS 

    print 'Old size'
    exec sp_configure 'max text repl size' 
    
    print ' Setting new size'
    exec sp_configure 'max text repl size', @NewSize
    
    print 'Reconfiguring'
    RECONFIGURE WITH OVERRIDE
    
    print 'New size'
    exec sp_configure 'max text repl size'