Wednesday, 30 October 2013

Issue with BLOB rows



Issue with BLOB rows-
Method 1 Set the SubscriptionStreams parameter of the Distribution Agent to a value of 1. Method 2 You can set the OleDbStreamThreshold parameter of the Distribution Agent to a value that is greater than the largest data size for binary large object (BLOB) columns that have to be replicated. Then, the Distribution Agent will not bind binary large object (BLOB) data as a stream. Starting from SQL Server 2008, binary large object (BLOB) data is bound as a stream when the data size of the binary large object (BLOB) data is less than the value of the OleDbStreamThreshold parameter. This behavior is true by default.

USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size';
GO

-- OUTPUT:
-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536
Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.
-- For SQL Server 2005
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', 2147483647 ; 
GO
RECONFIGURE;
GO

-- For SQL Server 2008 (and 2008 R2)
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', -1 ;
GO
RECONFIGURE;
GO

No comments:

Post a Comment