locked
Field Size Too Large RRS feed

  • Question

  • I have a strange issue with the creation of a snapshot replication proof of concept. I have 2 databases that have identical schema. I have set the 2 databases to replicate a single table to a subscriber. I configured the publisher to replicate all of the columns in the table and also selected "keep existing object unchanged" in the article properties for both Publications.

    I create the subscriber for the first database and it creates the subscriber schema and populates the data just fine. I then create the subscriber for the second database and it kicks off. It errors out with the "Field Size Too Large" and does not load any of the data from the snapshot.

    I initially thought it might be some bad data but if I load the schema and data from the second database first and then load the data from the first database it gives the exact same error.

    So next step was to find out which column was giving the problem. After many deletes and recreations of the publication using an increasing number of the tables columns - I found that one column was causing the problem. It is a datetime column that we use for a "RowLastModified" feature. It is a standard datetime column with a default value of getdate(). I visually scanned the column of the 2 tables (one has about 1000 records and the other about 300) and all looks fine.

    Too add to my confusion - I added a second table to the publisher that also has the same column that is populated the same way and it works just fine.

    Any suggestions of how to proceed?

    Thursday, May 31, 2012 8:08 PM

Answers

All replies

  • By such circumstances, I would like to give preference for data cleansing resolution through:

    • Checking if any old data entity stored inside this column has improper date format....

    • Checking any catalog or table or schema corruption exists there using the below commands:

    DBCC CheckDB ('workshops') WITH NO_INFOMSGS

    dbcc checkdbtable ('Tablename')

    If fixed the record having any kind of corruption , you have to buffer it somewhere and remove form that table till remediating it then you could return it back to the table.

    BTW, all of such data cleansing steps could be done easily using DQS (Data Quality Service ) of 2012 , thereby please it is much worthwhile to be worked out   


    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

    Friday, June 1, 2012 7:32 AM
  • Can I see the table schema? What version of sql is this?

    Are both databases replicating the same article to the same database and are they both replicating the data to the same table?

    If so are you using the keep existing object unchanged option?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, June 1, 2012 2:02 PM
    Answerer
  • I have already run the DBCC checks and both tables are fine. Just to check I added a filter to the article properties to only replicate the first record from each table. It still gives the same error. So it doesn't appear to be bad data.

    Here is the schema. it is a 2008 server. Both databases are feeding the same database and table. Yes I have selected the keep existing object unchanged option.

    Again please note that if I remove the RowLastModified_TimeStamp column from the article selection, everything works just fine.

    Thanks for all suggestions :-)


    CREATE TABLE [dbo].[DOCUMENT](
    [DTITLE] [nvarchar](1024) NOT NULL,
    [DNOTE] [nvarchar](max) NULL,
    [CATEGORY] [nvarchar](75) NULL,
    [RECEIVED] [datetime] NULL,
    [REVISION] [int] NOT NULL,
    [DSTATUS] [int] NOT NULL,
    [STATUSDATE] [datetime] NOT NULL,
    [DDISPOSIID] [int] NULL,
    [AUTHDAYS] [int] NULL,
    [PUBDATE] [datetime] NULL,
    [SETPAGES] [numeric](8, 2) NULL,
    [PRINTPAGES] [varchar](20) NULL,
    [PUBVOLUME] [nvarchar](10) NULL,
    [PUBISSUE] [nvarchar](10) NULL,
    [TOC] [int] NULL,
    [PUBDNUMBER] [varchar](100) NULL,
    [SUBNUMBER] [varchar](100) NOT NULL,
    [LASTUPDATE] [datetime] NOT NULL,
    [DOCUMENTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [DDISPOSIDATE] [datetime] NULL,
    [ORIGINAL_SUBMISSION_START_DATE] [datetime] NOT NULL,
    [AUTHORITATIVE_VERSION] [varchar](1) NULL,
    [DCATEGOID] [int] NOT NULL,
    [MINREVREQD] [int] NOT NULL,
    [INVITERESPONSEDAYS] [int] NOT NULL,
    [DISPMANUNOTESFLAG] [bit] NOT NULL,
    [DOI] [nvarchar](256) NULL,
    [ABSTRACT_TEXT] [nvarchar](max) NULL,
    [SECTIONID] [int] NOT NULL,
    [ENTER_ARTICLE_TITLE] [bit] NOT NULL,
    [SELECT_ARTICLE_TYPE] [bit] NOT NULL,
    [ADD_EDIT_REMOVE_AUTHORS] [bit] NOT NULL,
    [SELECT_SECTION_CATEGORY] [bit] NOT NULL,
    [SUBMIT_ABSTRACT] [bit] NOT NULL,
    [ENTER_KEYWORDS] [bit] NOT NULL,
    [SELECT_CLASSIFICATIONS] [bit] NOT NULL,
    [ENTER_COMMENTS] [bit] NOT NULL,
    [REQUEST_EDITOR] [bit] NOT NULL,
    [ATTACH_FILES] [bit] NOT NULL,
    [DIRECT_TO_EDITOR_ID] [int] NULL,
    [CORR_EDITOR_ID] [int] NULL,
    [DAYSTOREVISE] [int] NOT NULL,
    [EDITORIALPROCESSCLOSED] [bit] NOT NULL,
    [FINALDECISIONDATE] [datetime] NULL,
    [COMMENTARIES_INVITED] [bit] NOT NULL,
    [COMMENTARIES_CLOSED] [bit] NOT NULL,
    [RELATED_DOCUMENTID] [int] NULL,
    [ZIP_STATUS_ID] [int] NOT NULL,
    [ZIP_DATE] [datetime] NULL,
    [EDIT_SUBMISSION_STATUS_ID] [int] NOT NULL,
    [EDIT_SUBMISSION_STATUS_DATE] [datetime] NULL,
    [CHOOSE_REGION] [bit] NOT NULL,
    [REGIONID] [int] NULL,
    [ADDITIONAL_INFORMATION] [bit] NOT NULL,
    [OLD_DIRECT_TO_EDITOR_ID] [int] NULL,
    [OLD_CORR_EDITOR_ID] [int] NULL,
    [RC_RESULTS_FOR_AUTH_VERSION] [int] NOT NULL,
    [SHORT_TITLE] [nvarchar](768) NULL,
    [FULL_TITLE_LIMIT_COUNT] [int] NULL,
    [SHORT_TITLE_LIMIT_COUNT] [int] NULL,
    [ABSTRACT_LIMIT_COUNT] [int] NULL,
    [COMMENTS_LIMIT_COUNT] [int] NULL,
    [LIMIT_KEYWORDS_COUNT] [int] NULL,
    [SUGGEST_REVIEWERS] [bit] NOT NULL,
    [OPPOSE_REVIEWERS] [bit] NOT NULL,
    [RESPOND_TO_REVIEWERS] [bit] NOT NULL,
    [DISPTECHNOTESFLAG] [bit] NOT NULL,
    [TECHNOTES] [nvarchar](max) NULL,
    [TECHCOMMENTS] [nvarchar](max) NULL,
    [PRODUCTION_NOTES] [nvarchar](max) NULL,
    [PRODUCTION_STATUS] [int] NOT NULL,
    [DATE_PRODUCTION_WAS_INITIATED] [datetime] NULL,
    [DATE_PRODUCTION_WAS_COMPLETED] [datetime] NULL
    ) ON [PRIMARY]
    SET ANSI_PADDING OFF
    ALTER TABLE [dbo].[DOCUMENT] ADD [START_PAGE] [varchar](10) NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [END_PAGE] [varchar](10) NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [ONLINE_PUB_DATE] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [CORR_PROD_ED_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [OLD_CORR_PROD_ED_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [CORR_PROD_ED_ROLEID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [CORR_PROD_ED_ROLEFAMILY] [varchar](20) NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [TARGET_ONLINE_PUB_DATE] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [ACTUAL_ONLINE_PUB_DATE] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [IMPORT_DATE] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [PRE_RESCINDED_STATUS_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [DATE_FIRST_ENTERED_PRODUCTION] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [PWID_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [Row_LastModified_TimeStamp] [datetime] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [FINAL_DECISION_EDITOR_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [CREATED_USING_SHORT_INTERFACE] [bit] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [FIRST_RECEIPT_DATE] [datetime] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [TRANSFER_FROM_STATUS] [int] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [TRANSFER_TO_STATUS] [int] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [TRANSFERRED_FROM_SITE_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [TRANSFERRED_TO_SITE_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [ACTIVE_DISCUSSION] [bit] NOT NULL
    SET ANSI_PADDING ON
    ALTER TABLE [dbo].[DOCUMENT] ADD [ISO_REGION_CODE] [varchar](2) NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [CUSTOM_REGION_ID] [int] NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [NUMDAYS_UNINVITE_AFTER_REVIEW_DUEDATE] [int] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [UNASSIGN_REVIEWER_WITH_REVIEW_INPROGRESS] [bit] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [IS_EDITORIAL_SUBMISSION] [bit] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [FINAL_DISPOSITION_SET_DURING_SUBMISSION_PROCESS] [bit] NOT NULL
    ALTER TABLE [dbo].[DOCUMENT] ADD [journal_id] [int] NOT NULL


    Friday, June 1, 2012 2:13 PM
  • You've got some [nvarchar](max) columns in the table; the data in the source table may be larger than the setting in sp_configure 'max text repl size (B)'.  The default setting for this option is 65536; the data in your nvarchar(max) columns may exceed this limit.  Try:

    sp_configure 'show advanced options', 1;
    reconfigure;
    
    sp_configure 'max text repl size (B)', 2147483647;
    reconfigure;
    HTH.
    Friday, June 1, 2012 4:07 PM
  • OK so I have identified the problem. Even though I compared the 2 source table schema's (I was looking at datatypes) they are not truly identical. In one table the RowLastModified_Timestamp column is not in the same order as the other table. Now my question is - does this mean that BCP does not look at column names when it populates the table? It sure looks that way. It appears that it is putting the data in on a column number to column number basis which would explain the error.

    Is there any way around this? It was an easy fix for my test as I just manually went in and move the column up to match the other table. But my final production run will have hundreds of databases feeding the subscriber... 

    Let me know if I am not clear enough on what the REAL problem is now.

    Friday, June 1, 2012 4:38 PM
  • You need to use a custom sync object which has the correct order of columns.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by amber zhang Monday, June 4, 2012 2:21 AM
    • Marked as answer by amber zhang Friday, June 8, 2012 1:44 AM
    Friday, June 1, 2012 6:14 PM
    Answerer