locked
Field size too large RRS feed

  • Question

  • I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.

    The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor:
    *The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037
    *Field size too large

    The table looks like this:
    Code Snippet

    CREATE TABLE virtualdatalocations_waitingqueues (
      dataid int ,
      personid int ,
      queueid int ,
      CONSTRAINT FK_vw_dataid
        FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE ,
      CONSTRAINT FK_vw_personid
        FOREIGN KEY(personid) REFERENCES persons(id),
      CONSTRAINT FK_vw_queueid
        FOREIGN KEY(queueid)REFERENCES waitingqueues(id)
    );



    It used to run fine in the past. I couldn't find any help on google or on forums.

    Any help or comments are greatly appreciated.
    Friday, May 9, 2008 1:58 PM

Answers

  •  

    I had this same problem.  In comparing the schema of the table in both the publisher and subscriber, they appeared to be identical.  However, I had to drop the table on the subscriber and recreate it using a create table script generated on the publisher.  Once I did that, the snapshot proceeded without any more errors.  HTH.
    Tuesday, June 10, 2008 1:59 AM

All replies

  •  

    I had this same problem.  In comparing the schema of the table in both the publisher and subscriber, they appeared to be identical.  However, I had to drop the table on the subscriber and recreate it using a create table script generated on the publisher.  Once I did that, the snapshot proceeded without any more errors.  HTH.
    Tuesday, June 10, 2008 1:59 AM
  • Same problem but dropping the table on the subscriber as suggested by JStipp is not an option: our subscriptions have a lot of data and only part of it needs to be reinitialized. A full reinitialization will take days. The problem happens when trying to push the snapshot to the subscriber without recreating the schema, i.e. send the snapshot data only.

     

    Differences in table definitions are not the problem - it can be reproduced by creating a test table from one of the real tables using SELECT INTO and then creating a publication of it. This works if you let the snapshot create the table on the subscriber but, if we script the table and create it on the subscriber and have the snapshot initialize data only then it fails.

     

    This is a new problem since we upgraded our publisher / distributor to 2005. The subscribers are still 2000. However, it is not consistent - at least one subscriber was recently partially reinitilized ok and it is on exactly the same version number as the subscribers that fail.

     

    I have found several postings of this error on various sites - it seems to be a common problem that has been around for a couple of years. The only solution so far offered is that it is caused by some schematic difference between publisher(s) and subscriber and you have to either drop the subscription table or (if you have multiple publishers like we do) drop/recreate tables on one of your publishers (e.g. see http://blogs.sqlservercentral.com/jeffrey_yao/archive/2008/06/03/replication-error-quot-field-size-too-large-quot.aspx). Neither of these are practical in our case and, from the test I describe above and apparently from our live situation, there would appear to be more to it than this.

     

    Come on Microsoft, this is a real pain the neck. We need the real answer please.

    Tuesday, August 19, 2008 6:13 PM
  • Hi guys,

     

    I'm experiencing the same 'Field size too large' issue with merge replication.

     

    SETUP:

    Publisher: Merge publication, Enabled for Web synchronization

    Subscriber: Pull anonymous subscription to merge publication

     

    BACKGROUND:

    There are table articles in the merge publication which are allowed for updates on a subscriber. Changes flow only in one direction – from publisher to subscriber. These articles contain customer specific data. Moreover, identity field in the tables is being used as a foreign key in other tables containing customer specific data too.

     

    SCENARIO:

    1. Create subcription - OK
    2. Synchronize - OK
    3. Drop subscription - OK
    4. Create subscription - OK
    5. Synchronize - FAILED

    Log cut (logging level set to 3, synchronization is done using replmerge utility):

    2008-12-06 12:30:38.337 Disconnecting from OLE DB Subscriber 'WS\SQLEXPRESS'
    2008-12-06 12:30:38.347 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2008-12-06 12:30:38.357 OLE DB Subscriber 'WS\SQLEXPRESS': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2008-12-06 12:30:38.397 Percent Complete: 100
    2008-12-06 12:30:38.397 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2008-12-06 12:30:38.407 Percent Complete: 0
    2008-12-06 12:30:38.417 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147201001
    Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2008-12-06 12:30:38.417 Percent Complete: 0
    2008-12-06 12:30:38.457 Category:AGENT
    Source:  WS\SQLEXPRESS
    Number:  20037
    Message:
    The process could not bulk copy into table '"dbo"."Content"'.
    2008-12-06 12:30:38.457 Percent Complete: 0
    2008-12-06 12:30:38.468 Category:NULL
    Source:  Microsoft SQL Native Client
    Number:  0
    Message:
    Field size too large
    2008-12-06 12:30:38.468 Percent Complete: 0
    2008-12-06 12:30:38.478 Category:NULL
    Source: 
    Number:  20253
    Message: To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options.
    2008-12-06 12:30:38.478 Percent Complete: 0
    2008-12-06 12:30:38.488 Category:NULL
    Source: 
    Number:  20253
    Message:
    bcp "MCOSS"."dbo"."Content" in "C:\DOCUME~1\User\LOCALS~1\Temp\MIL-WEB01_MCOSS_MCOSW_MCOSS\Content_143.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SWS\SQLEXPRESS -T -w
    2008-12-06 12:30:39.399 Disconnecting from OLE DB Subscriber 'WS\SQLEXPRESS'
    2008-12-06 12:30:39.459 Disconnecting from OLE DB Subscriber 'WS\SQLEXPRESS'
    2008-12-06 12:30:39.459 Disconnecting from OLE DB Subscriber 'WS\SQLEXPRESS'
    2008-12-06 12:30:39.469 Disconnecting from OLE DB Subscriber 'WS\SQLEXPRESS'

     

    When I tried to use the command

    bcp "MCOSS"."dbo"."Content" in "C:\DOCUME~1\User\LOCALS~1\Temp\MIL-WEB01_MCOSS_MCOSW_MCOSS\Content_143.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SWS\SQLEXPRESS -T -w

    I’ve got the following error message:

    Starting copy...
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

    0 ro copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1

     

    APPLIES TO:

    Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86)   Aug  5 2008 12:31:12   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windo NT 5.1 (Build 2600: Service Pack 3)

     

     

    Please help!

     

    Thank you!

     

    Alexander

     

    Wednesday, December 10, 2008 12:59 PM
  • You'll have to analyze the bcp file and figure out why it fails to bcp into your table.

    Wednesday, December 10, 2008 8:20 PM
  • I just had this problem. What I did was edit the dynamic snapshot location in the dynamic snapshot job for a new folder. I generated the dynamic snapshot again and sent it down and this time it worked.
    Wednesday, December 10, 2008 9:13 PM
    Answerer
  • We've found the reason the issue together with Microsoft Support escalation engineers. The issue specifically raised for the articles whose @pre_creation_cmd was 'Delete data. If article has a row filter, delete only data that matches the filter'. The point is that Merge Agent does not check/correct articles schema but looks only at the name which is unfortunate. In fact, what happens is when we drop subscription - articles get altered (drop rowguid), but when we create subscription and first initialize they don't (the necessary rowguid field does not get created).

    So, to prevent the issue to happen we must compare schema of the articles with this type of @pre_creation_cmd with their schema on the publisher and add missing columns keeping the sequence of the columns exactly the same as on the publisher - between steps 3 and 4. Then we can create subscription (step 4) and successfully synchronize (step 5)!
    Everything is possible
    Saturday, February 21, 2009 1:52 PM
  • Tks for the answer, I have these problem but I don´t understand your solution, you can´t explain to me how to use these command @pre_creation_cmd.

    Regards  
    Techico en Sistemas
    Wednesday, March 4, 2009 6:08 PM
  • this worked great!!  Thanks for the tip.  problem resolved
    Wednesday, September 23, 2009 6:17 AM
  • We have the same issue, however all above solution won't apply. Since we have to create Snapshot frequently, and various client machines need to drop/create subscription periodically and pull the new snapshot.

    The failed messages are not always the same, but most of them are complaining about bulk-copying one of our largest table. some time it compains about other tables, even MSMerge_contents system table.

    Any idea? could this cause by network issue.

    BTW, it was working prefectly before with no issue.

    Thanks,
    --Cheng
    Friday, October 9, 2009 9:35 PM
  • Hi Cheng,

    In my case the issue was concerned with the fact that after a subscription is dropped, the rowguid columns are gone. Then, when we create subscription, the merge agent does not check an article schema on subscriber - it checks only if the article exists. Hence, when the subscription is re-created the rowguid columns are not there. Somehow this affected only articles with  @pre_creation_cmd='Delete data. If article has a row filter, delete only data that matches the filter'. In the end, when the merge agent is at the stage of loading the data, it fails with the error "Field size too large", because a rowguid column is missing in the target table.

    My suggestion would be to try narrowing your case down to more specific error messages. Also, since a system replication table is involved it would be smart to escalate the issue to Microsoft Support.
    Everything is possible
    Saturday, October 10, 2009 6:28 PM