none
Merge Replication: Process could not enumerate changes

    Question

  • Running merge replication (pull) downloads from Pub to subscribers work fine

    Uploading a change from subscriber to publisher generates message (logging turned on):

    The merge process could not enumerate changes at the subscriber"
    Source: Merge ReplicationProvider

    Number: -2147200999
    No other errors
    Removed and rebuild replication, problem remains

    Help needed as to possible causes of this problem

    • Edited by Alien039 Thursday, February 21, 2013 8:48 PM
    Thursday, February 21, 2013 8:46 PM

All replies

  • Change querytimeout to a larger value. You might be experiencing blocking on the publisher. Use profiler or sp_who2 to see if this is a user process or replication.

    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

    Thursday, February 21, 2013 11:34 PM
    Moderator
  • Thanks for the feedback

    Increased the querytimeout from 300 to 3000, but no result
    Checked in profiler, no errors or blockages

    To eliminate possible causes, we setup a new subscriber, empty database and no filtering
    This works. Can a filter on the replication cause the message we get?

    For reference included the logfile:

    2013-02-21 12:56:07.721 Connecting to OLE DB Subscriber at datasource: 'SDMSQL01', location: '', catalog: 'DBWORKS_TEST_PANT_2', providerstring: '' using provider 'SQLNCLI10'
    2013-02-21 12:56:07.724 OLE DB Subscriber: SDMSQL01
       DBMS: Microsoft SQL Server
       Version: 10.00.2531
       catalog name: DBWORKS_TEST_PANT_2
       user name: EMEA\rl62sdm
       API conformance: 0
       SQL conformance: 0
       transaction capable: 1
       read only: F
       identifier quote char: "
       non_nullable_columns: 0
       owner usage: 15
       max table name len: 128
       max column name len: 128
       need long data len:
       max columns in table: 1000
       max columns in index: 16
       max char literal len: 131072
       max statement len: 131072
       max row size: 131072
    2013-02-21 12:56:07.725 Connecting to OLE DB Publisher at datasource: 'SI0APP202', location: '', catalog: 'DBWorks_Publisher_2', providerstring: '' using provider 'SQLNCLI10'
    2013-02-21 12:56:07.879 OLE DB Publisher: SI0APP202
       DBMS: Microsoft SQL Server
       Version: 11.00.2100
       catalog name: DBWorks_Publisher_2
       user name: dbo
       API conformance: 0
       SQL conformance: 0
       transaction capable: 1
       read only: F
       identifier quote char: "
       non_nullable_columns: 0
       owner usage: 15
       max table name len: 128
       max column name len: 128
       need long data len:
       max columns in table: 1000
       max columns in index: 16
       max char literal len: 131072
       max statement len: 131072
       max row size: 131072
    2013-02-21 12:56:08.096 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-21 12:56:08.097 OLE DB Subscriber 'SDMSQL01': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-21 12:56:08.098 Percent Complete: 0
    2013-02-21 12:56:08.099 No data needed to be merged.
    2013-02-21 12:56:08.099 OLE DB Distributor 'SI0APP202': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-21 12:56:08.127 OLE DB Subscriber 'SDMSQL01': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-21 12:56:08.128 Percent Complete: 0
    2013-02-21 12:56:08.129 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-21 12:56:08.129 OLE DB Distributor 'SI0APP202': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-21 12:56:08.189 Percent Complete: 0
    2013-02-21 12:56:08.189 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147200999
    Message: The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-21 12:56:08.189 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.189 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.190 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.190 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.190 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.190 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.191 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.191 Disconnecting from OLE DB Subscriber 'SDMSQL01'
    2013-02-21 12:56:08.191 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.192 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.192 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.192 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.192 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.193 Disconnecting from OLE DB Publisher 'SI0APP202'
    2013-02-21 12:56:08.193 Disconnecting from OLE DB Distributor 'SI0APP202'
    2013-02-21 12:56:08.193 Disconnecting from OLE DB Distributor 'SI0APP202'
    2013-02-21 12:56:08.194 The merge process will restart after waiting 60 second(s)...

    Friday, February 22, 2013 7:26 PM
  • Hi Alien039,

    This issue can occur due to poor network connection, we can try to replicate a small database and check the result. If it works, we need to improve our network. There are some similar issues on the following links, you can refer to them.

    http://www.sqlservercentral.com/Forums/Topic1340424-391-1.aspx
    http://www.mombu.com/microsoft/sql-server-replication/t-merge-replication-could-not-enumerate-changes-at-the-publisher-667064.html


    Allen Li
    TechNet Community Support

    Monday, February 25, 2013 6:24 AM
    Moderator
  • Hi Allen,

    Connection is stable and latency is low, although unlikely we'll check your suggestion
    Found an exact match in previous posts, this however did not have a clear solution

    http: social.technet.microsoft.com/Forums/es-US/sqlreplication/thread/dc7a16f0-3e55-4c99-9fa1-42f96f379aab

    Will check the suggestions in this thread regarding possible problem with generations
    Anything suggestions as what more to check?

    Thanks,

    Alien039

    Monday, February 25, 2013 7:13 PM
  • The question in my mind is that if you updated hundereds of rows on the subscriber why is the singleton insert proc firing instead of the batch one. We need to figure this out.

    Are you using a custom profile?


    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

    Monday, February 25, 2013 8:37 PM
    Moderator
  • Hi Hilary,

    Profile is a copy of the Default profile, only change is the increased querytimeout
    Also added logging (level 4) directly to the merge agent job 
    Checked the genhistory table, but no genstatus=4

    Have opened a call with Microsoft to check if they've seen something similar

    Below the output of the level 4 log, any suggestions are greatly appreciated:

    2013-02-26 10:20:01.561 Uploading data changes to the Publisher
    2013-02-26 10:20:01.564 Repl Agent Status: 3
    2013-02-26 10:20:01.565 OLE DB Subscriber 'FE0VMC0277': {call sp_MSreleasemakegenerationapplock}
    2013-02-26 10:20:01.566 OLE DB Distributor 'SI0APP202': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-26 10:20:01.569 OLE DB Subscriber 'FE0VMC0277': {call sys.sp_MSenumgenerations90 (1,?,0,?,?,?) }
    2013-02-26 10:20:01.573 OLE DB Publisher 'SI0APP202': {call sp_MScheckexistsgeneration (?, ?) }{call sp_MScheckexistsgeneration (?, ?) }{call sp_MScheckexistsgeneration (?, ?) }{call sp_MScheckexistsgeneration (?, ?) }
    2013-02-26 10:20:01.576 OLE DB Publisher 'SI0APP202': {call sys.sp_MSinsertgenhistory (?,?,?,?,?,?,90) }
    2013-02-26 10:20:01.579 OLE DB Subscriber 'FE0VMC0277': {call sys.sp_MSenumdeletesmetadata(?,?,?,?,?,?,0,?,?,90)}
    2013-02-26 10:20:01.579 OLE DB Subscriber 'FE0VMC0277': {call sp_MSenumcolumns (?,?)}
    2013-02-26 10:20:01.594 OLE DB Subscriber 'FE0VMC0277': {call sp_MSenumcolumns (?,?)}
    2013-02-26 10:20:01.597 OLE DB Subscriber 'FE0VMC0277': {call sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0,'1DA620AA-9A46-42EE-A057-3B3F4753E89B')}
    2013-02-26 10:20:01.608 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-26 10:20:01.612 OLE DB Subscriber 'FE0VMC0277': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-26 10:20:01.616 Percent Complete: 100
    2013-02-26 10:20:01.619 No data needed to be merged.
    2013-02-26 10:20:01.621 Repl Agent Status: 4
    2013-02-26 10:20:01.623 OLE DB Distributor 'SI0APP202': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-26 10:20:01.627 OLE DB Subscriber 'FE0VMC0277': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-26 10:20:01.632 Percent Complete: 100
    2013-02-26 10:20:01.635 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-26 10:20:01.637 Repl Agent Status: 6
    2013-02-26 10:20:01.639 OLE DB Distributor 'SI0APP202': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2013-02-26 10:20:01.649 Percent Complete: 0
    2013-02-26 10:20:01.651 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147200999
    Message: The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2013-02-26 10:20:01.652 Repl Agent Status: 3

    Tuesday, February 26, 2013 7:57 PM
  • This fails within seconds. I think there is someting blocking/deadlocking on the subscriber. Can you run

    select * from sys.sysprocesses where blocked<>0 and dbid=db_id()

    in the subscriber database to see what it reports?


    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

    Wednesday, February 27, 2013 3:55 PM
    Moderator
  • Unfortunately, no result
    Dubble checked for blocking processes, but none found (query did not return anything)
    Although logfile is showing that Sub, Pub and Dist are happily chatting, can permissions cause this message?

    Thursday, February 28, 2013 8:04 PM
  • It is not a permissions problem. If it was you would not get as far as you are.

    Percomputed partitions would help to alleviate this problem.


    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, March 01, 2013 1:48 AM
    Moderator