|
|
You're in the
sql server replication
group:SQL Merge Replication Errors
sql server replication:
I have a replication setup that uses Dynamic filters and dynamic snapshot
folders. I also use the keep_partition_changes flag to define which changes are required at each subscriber . The end target is replication to SQL CE. During testing I am using a pull subscription to a sql database. (I have tested this issue with subscriptions from a device and still get the errors). The following is an example of the errors I get. Last Command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)} Error Message : Failed to enumerate changes in the filtered articles. Error Details: Failed to enumerate changes in the filtered articles. (Source: Merge Replication Provider (Agent); Error number: -2147200925) --------------------------------------------------------------------------------------------------------------- SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. (Source: machine_name (Data source); Error number: 0) --------------------------------------------------------------------------------------------------------------- The merge process encountered an unexpected network error. The connection to Publisher 'machine_name' is no longer available. (Source: Merge Process (Agent); Error number: -2147199469) --------------------------------------------------------------------------------------------------------------- This looks to me like it throws a big wobbly and bails out. Does this seem familiar to anyone? I am testing with multilpe subscribers and under some conditions it works for one or two of them while failing for the others. Change the conditions - rows to be filtered - and the error may occur now for those that worked before. Those that failed now work. Yes, it is that confusing. I am taking the approach that my join filters are too complicated and resolving these to be something much more simplistic, using a scheduled applicaiton to make the necessary definitions of what data should and should appear in a subscription. Sorry for the long windedness, but its best to get as much information out there as possible for a better chance of a reply. I look forward to hearing from you about where I might be going wrong. Regards
Thanks for this Hilary.
keep_partition_changes is a must for us as when sending changes to a SQL CE device, we have found that without it you get notification for records that are not related to the partition. This creates a negative user experience due to timing issues and can often confuse the issue for us when trying to maintain the system. In addition, we have a requirement to sometimes move data between partitions and it looks like this will help. Just to give you an idea of the things we are doing with our data, We need to loose data over a period of time and that time is contiguos for some of the data but not others. We need to be able to move customers and their associated data between territories and owners simultaneously. Sometimes the associated data need sto be moved because of which customers it now applies to. We have data that needs to be partitioned based on up to 3 or 4 other tables. In order to deal with this we have moved the complexity of determining what belongs where away from the pulication setup and added appropriate flags to the tables in order to make the pulicatino look a lot simpler than it actually is. And it hasn't been without its problems. :) Thanks again Steve P/S does the book you mention go into any detail about dealing with SQL Merge replication to SQL CE? This seems to be a difficult topic to find information on. [quoted text, click to view] "Hilary Cotter" wrote:
> Be careful about using keep_partition_changes. This is a great switch to use > if you find that the column you are filtering on changes from time to time. > > Consider a case when your filter looks like this > > select * from <publisher tables> where state='ca' > > If you update your state value for rows on your subscriber to lets say 'NY' > these changes will be removed from CA and inserted into NY. Merge > replication will do this very efficiently if you do these partition > realignments from time to time as it keeps tracking metadata on the > publisher and subscriber. It fires the following procs to do this > > sp_MSproxiedmetadata > sp_MSproxiedmetadata > xp_proxiedmetadata > sp_MSproxiedmetadatabatch > sp_MSproxiedmetadatalightweight > > So, you will see as part of your normal merge operation these procs fired > with abandon keeping meta data around which you probably don't use. Turning > this off unless you really need it is a good idea. > > The errors you are getting are transitory and the next time your merge agent > runs they will likely clear. The dump error is more serious. > > Simple join conditions are always a good idea. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com
Be careful about using keep_partition_changes. This is a great switch to use
if you find that the column you are filtering on changes from time to time. Consider a case when your filter looks like this select * from <publisher tables> where state='ca' If you update your state value for rows on your subscriber to lets say 'NY' these changes will be removed from CA and inserted into NY. Merge replication will do this very efficiently if you do these partition realignments from time to time as it keeps tracking metadata on the publisher and subscriber. It fires the following procs to do this sp_MSproxiedmetadata sp_MSproxiedmetadata xp_proxiedmetadata sp_MSproxiedmetadatabatch sp_MSproxiedmetadatalightweight So, you will see as part of your normal merge operation these procs fired with abandon keeping meta data around which you probably don't use. Turning this off unless you really need it is a good idea. The errors you are getting are transitory and the next time your merge agent runs they will likely clear. The dump error is more serious. Simple join conditions are always a good idea. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "SteveM" <SteveM@discussions.microsoft.com> wrote in message news:12810A1E-2D64-44BF-AE64-A6EAAFC01E2F@microsoft.com... >I have a replication setup that uses Dynamic filters and dynamic snapshot > folders. > > I also use the keep_partition_changes flag to define which changes are > required at each subscriber . The end target is replication to SQL CE. > > During testing I am using a pull subscription to a sql database. (I have > tested this issue with subscriptions from a device and still get the > errors). > > The following is an example of the errors I get. > > Last Command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)} > > Error Message : Failed to enumerate changes in the filtered articles. > > Error Details: > Failed to enumerate changes in the filtered articles. > (Source: Merge Replication Provider (Agent); Error number: -2147200925) > --------------------------------------------------------------------------------------------------------------- > SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005 > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. > (Source: machine_name (Data source); Error number: 0) > --------------------------------------------------------------------------------------------------------------- > The merge process encountered an unexpected network error. The connection > to > Publisher 'machine_name' is no longer available. > (Source: Merge Process (Agent); Error number: -2147199469) > --------------------------------------------------------------------------------------------------------------- > > > This looks to me like it throws a big wobbly and bails out. > > Does this seem familiar to anyone? > > I am testing with multilpe subscribers and under some conditions it works > for one or two of them while failing for the others. Change the > conditions - > rows to be filtered - and the error may occur now for those that worked > before. Those that failed now work. Yes, it is that confusing. > > I am taking the approach that my join filters are too complicated and > resolving these to be something much more simplistic, using a scheduled > applicaiton to make the necessary definitions of what data should and > should > appear in a subscription. > > Sorry for the long windedness, but its best to get as much information out > there as possible for a better chance of a reply. > > I look forward to hearing from you about where I might be going wrong. > > Regards > Steve
Hi again Hilary,
I'm interested to know more about the comment you made below. Any idea what causes these transitory errors? Do I need to tell my client to expect these errors? That can't be acceptable behaviour can it? Ar ewe getting these errors because of our settings for the publication or are they related to the joins? [quoted text, click to view] "Hilary Cotter" wrote: [quoted text, click to view] > The errors you are getting are transitory and the next time your merge agent
> runs they will likely clear. The dump error is more serious. >
Sometimes setting QueryTimeout to larger values will clear this error.
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "SteveM" <SteveM@discussions.microsoft.com> wrote in message news:5696327B-B7F5-4951-A48D-071799E44111@microsoft.com... > Hi again Hilary, > > I'm interested to know more about the comment you made below. > > Any idea what causes these transitory errors? > > Do I need to tell my client to expect these errors? That can't be > acceptable > behaviour can it? > > Ar ewe getting these errors because of our settings for the publication or > are they related to the joins? > > "Hilary Cotter" wrote: > > >> The errors you are getting are transitory and the next time your merge >> agent >> runs they will likely clear. The dump error is more serious. >> >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |