Friday, July 27, 2012

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


Error:
Length of LOB data (78862) to be replicated exceeds configured maximum 65536
Scenario:
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
Solution:
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.

Search Value in Database

/*
 
*****************************************************************************************
Function: This stored procedure is used to search punctuation character or text content by scanning all table columns avalilable
in specified MSSQL Server Database.
*****************************************************************************************

This stored procedure is searched for following Data Type only
1) Text
2) Varchar
3) Nvarchar

This stored procedure has  following optional parameters.
1) @IsBlackBox
      This parameter defined whether stored procedure is running on Black Box or any other MS SQL Server Database.
      -     Data type of this parameter is INT (Integer).
      -     If stored procedure needs to run  on Black Box then set this parameter value needs to set to 1 (numeric one)
      -     If stored procedure needs to run on other MS SQL database server Parameter value needs to set to 0 (numeric zero)
      -     Default parameter value is 1 (numeric one)
     
2) @SearchCharacters
      This parameter defined for which punctuation / special character needs to be searched.
      -     Data type of this parameter is NVARCHAR (supports Unicode)
      -     Pass one or more punctuation /special  characters need to be searched. i.e. ‘;’~’ (Please do not add any delimiters)
      -     Default parameter value is ‘^0-9A-Za-z’  (Retrieve list of content  which has data value except alphanumeric)
     
3)  @SearchString
      This parameter defined for which string value needs to be searched.
      -     Data type of this parameter is NVARCHAR (supports Unicode)
      -     Pass one string needs to be searched. i.e. ‘John’
      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the content)
      *     Note: If  @SearchCharacters parameter passed with a value then this parameter value will be ignored.
                  This parameter works when @SearchCharacters value is blank.
                 
4) @Column
      This parameter defined for which column needs to be searched.
      -     Data type of this parameter is VARCHAR
      -     Pass column name need to be searched. i.e.  ‘pname’
      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the columns )

4) @Table
      This parameter defined for which table needs to be searched.
      -     Data type of this parameter is VARCHAR
      -     Pass table name need to be searched. i.e.  ‘xpat’
      -     Default parameter value is ‘ ’ [blank string]  (Retrieve all the tables )

*****************************************************************************************
Execution Methods
1) EXECUTE BlackBox_Search
            -Search with parameters default value
           
2) EXECUTE BlackBox_Search 1,';','','',''
            -search on black box schema for ';' character in all columns of all tables
                 
3)EXECUTE BlackBox_Search 1,'','john','',''
            -search on black box schema for value contains string 'john' in all columns of all tables

3)EXECUTE BlackBox_Search 1,'','john','plname','xpat'
            -search on black box schema for value contains string 'john' in plname column of xpat table

*****************************************************************************************                 

*/


CREATE PROCEDURE BlackBox_Search
(
      @IsBlackBox INT = 1, -- 1 for true , 0 for false
      @SearchCharacters NVARCHAR(200) = N'', -- Pass the Character/s needs to be searched
      @SearchString NVARCHAR(2000) = N'', -- Pass the string needs to be searched
      @Column VARCHAR(200) = '', -- Pass the column name
      @Table VARCHAR(200) = '' -- Pass the table name
)

AS
BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*****************************************************************************************/
-- Table Declaration
/*****************************************************************************************/
CREATE TABLE #TMP
(
      ID NUMERIC(18,0) IDENTITY,
      TableName VARCHAR(1000),
      ColumnName VARCHAR(1000),
      ColumnType VARCHAR(1000)
)

CREATE TABLE #Results
(
      ID NUMERIC(18,0) IDENTITY,
      IID NUMERIC(18,0) DEFAULT NULL,
      TableName NVARCHAR(2000) DEFAULT NULL,
      ColumnName NVARCHAR(2000) DEFAULT NULL,
      --ColumnType NVARCHAR(2000),
      ColumnValue NVARCHAR(2000)
)

/*****************************************************************************************/
-- Variable Declaration
/*****************************************************************************************/

DECLARE @Query NVARCHAR(2000)
SET @Query = N''

DECLARE @RowCount INT
SET @RowCount = 0


DECLARE @CurrentTableName NVARCHAR(1000)
DECLARE @CurrentColumnName NVARCHAR(1000)

/*****************************************************************************************/
-- Parameter Filteration
/*****************************************************************************************/

SET @SearchCharacters = REPLACE(@SearchCharacters,'''','''''')
SET @SearchCharacters = REPLACE(@SearchCharacters,'%','')

SET @SearchString = REPLACE(@SearchString,'''','''''')
SET @SearchString = REPLACE(@SearchString,'%','')


/*****************************************************************************************/
-- SET SEARCH CHARCHTER CONDITION
/*****************************************************************************************/

DECLARE @WhereCondition NVARCHAR(200)
--SET @WhereCondition = N' LIKE N''%[^0-9A-Za-z .,;:]%'' collate Latin1_General_BIN'
IF (@SearchCharacters is null or rtrim(ltrim(@SearchCharacters)) = '')
      BEGIN
            SET @WhereCondition = N' LIKE N''%[^0-9A-Za-z]%'' collate Latin1_General_BIN'
      END
ELSE
      BEGIN
            SET @WhereCondition = N' LIKE N''%['+@SearchCharacters+']%'' collate Latin1_General_BIN'
      END


/*****************************************************************************************/
-- SET SEARCH STRING CONDITION
/*****************************************************************************************/

IF (@SearchCharacters IS NULL OR  rtrim(ltrim(@SearchCharacters)) = '')
BEGIN

      IF ((@SearchString IS NOT NULL AND  rtrim(ltrim(@SearchString)) <> ''))
      BEGIN
            SET @WhereCondition = N' LIKE N''%'+@SearchString+'%'''
      END

END


/*****************************************************************************************/
-- Fetch Data with Table name and Column name (Applied Filter as per the parameter passed)
/*****************************************************************************************/

INSERT INTO #TMP
 SELECT t.name AS TableName,
c.name AS ColumnName
,ty.name as ColumnType
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
AND T.name LIKE ( CASE WHEN @Table is NULL OR @Table = '' THEN '%' ELSE @table END)
AND C.name LIKE ( CASE WHEN @Column is NULL OR @Column = '' THEN '%' ELSE @Column+'' END)
INNER JOIN SYS.types AS ty ON C.user_type_id = TY.user_type_id
AND Ty.name in ('text','varchar','nvarchar')

ORDER BY t.name;


/*****************************************************************************************/
-- Query Building and Execution of Query
/*****************************************************************************************/

SELECT  @RowCount = COUNT(1)  FROM #TMP

IF (@RowCount > 0)
BEGIN
     
      WHILE (@RowCount > 0)
      BEGIN
                  SET @CurrentTableName = ''
                  SET @CurrentColumnName = ''
                  SET @Query = ''
                 
                  SELECT @CurrentTableName = TableName , @CurrentColumnName = ColumnName   FROM #TMP WHERE ID = @RowCount
                 
                  -- If search for Black Box
                  IF (@IsBlackBox = 1)
                        BEGIN
                                    IF (@CurrentTableName not like 'xCodeSystemMaster')
                                    BEGIN
                                           SET @Query = N'INSERT INTO  #Results
                                                                  SELECT  IID
                                                                  , '''+@CurrentTableName+''' AS TableName
                                                                  , '''+@CurrentColumnName+''' AS ColumnName
                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition
                                     
                                     END
                                     
                                     ELSE
                                           BEGIN
                                                 SET @Query = N'INSERT INTO  #Results
                                                                  SELECT  NULL AS IID
                                                                  , '''+@CurrentTableName+''' AS TableName
                                                                  , '''+@CurrentColumnName+''' AS ColumnName
                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition
                                           
                                           END
                       
                        END
                  ELSE
                        BEGIN
                       
                                     SET @Query = N'INSERT INTO  #Results
                                                                  SELECT  NULL AS IID
                                                                  , '''+@CurrentTableName+''' AS TableName
                                                                  , '''+@CurrentColumnName+''' AS ColumnName
                                                                  ,['+@CurrentColumnName+'] as ColumnValue FROM '+@CurrentTableName+' WHERE ['+@CurrentColumnName+']'+@WhereCondition
                       
                        END
                 
                 
                  -- Execute Build Query 
                   EXECUTE(@Query)
                 
                 
     
     
      SET @RowCount = @RowCount - 1
      END

END


/*****************************************************************************************/
-- Selection of Final Results
/*****************************************************************************************/


SELECT TableName,IID,ColumnName,ColumnValue as Value FROM #Results



/*****************************************************************************************/
-- Drop the Objects
/*****************************************************************************************/

DROP TABLE #TMP
DROP TABLE #Results


/*****************************************************************************************/

END