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