| Groups | Blog | Home |
sql server replication : Transactional replication - update does not follow row filter rules
this seems very odd. Let's confirm that the command being executed really contravenes the view used to filter the article. For this you could use sp_browsereplcmds, logging of the error on the distribution agent and check the text of the synchronization view. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi
I have an issue where inserts follow row filter rules OK, but updates and deletions don't seem to. I'm using transactional replication. Let's assume we have a table called Customers with a simple row filter of SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 The snapshot and distribution only copies over records with a key greater than 1000. Correct. If a record with a key greater than 1000 is updated or deleted, the transactions are distributed and the same update or deletion appear on the replicated database. Correct. However, if a record with a key less than 1000 gets updated or deleted, there should be nothing to distribute as the row doesn't meet the filter requirements and doesn't exist in the replicated database. But, we get the following error: Error: The row was not found at the Subscriber when applying the replicated command. This sort of implies it is expecting to find a row with the sub 1000 key in the replicated database. Why should this be? I can create a new distribution agent profile to ignore this 20598 error, but our customers are not comfortable with this. Anyone else experienced this? Any resolution? Am I missing something obvious?? Regards Ian Hingley Swift LG Ltd
Thanks Paul
I'll take a look and post back. Cheers Ian [quoted text, click to view] Paul Ibison wrote:
> Ian, > this seems very odd. Let's confirm that the command being executed really > contravenes the view used to filter the article. For this you could use > sp_browsereplcmds, logging of the error on the distribution agent and check > the text of the synchronization view. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Paul
What do you mean by (or how do I do): check the text of the synchronization view. Cheers Ian [quoted text, click to view] Paul Ibison wrote:
> Ian, > this seems very odd. Let's confirm that the command being executed really > contravenes the view used to filter the article. For this you could use > sp_browsereplcmds, logging of the error on the distribution agent and check > the text of the synchronization view. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com .
There could be two issues, a deferred update where the update is replace by
an delete followed by an insert - which is unlikely as both should be filtered out. Or perhaps you are not enforcing a cascading delete or update for replication. Can you check for this? -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] <ian.hingley@swiftlg.com> wrote in message news:1159960418.727872.65910@e3g2000cwe.googlegroups.com... > Hi > > I have an issue where inserts follow row filter rules OK, but updates > and deletions don't seem to. I'm using transactional replication. > Let's assume we have a table called Customers with a simple row filter > of > > SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 > > The snapshot and distribution only copies over records with a key > greater than 1000. Correct. > If a record with a key greater than 1000 is updated or deleted, the > transactions are distributed and the same update or deletion appear on > the replicated database. Correct. > > However, if a record with a key less than 1000 gets updated or deleted, > there should be nothing to distribute as the row doesn't meet the > filter requirements and doesn't exist in the replicated database. But, > we get the following error: > > Error: The row was not found at the Subscriber when applying the > replicated > command. > > This sort of implies it is expecting to find a row with the sub 1000 > key in the replicated database. Why should this be? I can create a > new distribution agent profile to ignore this 20598 error, but our > customers are not comfortable with this. > > Anyone else experienced this? Any resolution? Am I missing something > obvious?? > > Regards > > Ian Hingley > Swift LG Ltd >
Ian,
if you run the query below, it'll correlate the article names and the syncobject names. Using this, you'll be able to run sp_helptext on the syncobject associated with the articles and determine that they are really what you expect. SELECT syspublications.name AS Publication, sysobjects.name AS SynObject, sysobjects_1.name AS ArticleName FROM sysarticles INNER JOIN sysobjects ON sysarticles.sync_objid = sysobjects.id INNER JOIN syspublications ON sysarticles.pubid = syspublications.pubid INNER JOIN sysobjects sysobjects_1 ON sysarticles.objid = sysobjects_1.id Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com .
In enterprise manager, right click on your table, and select properties, and
then relationships. You should see it there. In SSMS, right click on your table, select properties, right click on a column and select relationships. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] <ian.hingley@swiftlg.com> wrote in message news:1160044091.034295.233090@k70g2000cwa.googlegroups.com... > Hi Hilary > > Thanks for your reply. > Can you tell me how I check: > > 'Or perhaps you are not enforcing a cascading delete or update > for replication'? > > Sorry if this is mundane stuff for you; I really rate replication when > it works, but struggle a bit when it goes wrong! > > Regards > > Ian > > > Hilary Cotter wrote: >> There could be two issues, a deferred update where the update is replace >> by >> an delete followed by an insert - which is unlikely as both should be >> filtered out. Or perhaps you are not enforcing a cascading delete or >> update >> for replication. Can you check for this? >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> <ian.hingley@swiftlg.com> wrote in message >> news:1159960418.727872.65910@e3g2000cwe.googlegroups.com... >> > Hi >> > >> > I have an issue where inserts follow row filter rules OK, but updates >> > and deletions don't seem to. I'm using transactional replication. >> > Let's assume we have a table called Customers with a simple row filter >> > of >> > >> > SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 >> > >> > The snapshot and distribution only copies over records with a key >> > greater than 1000. Correct. >> > If a record with a key greater than 1000 is updated or deleted, the >> > transactions are distributed and the same update or deletion appear on >> > the replicated database. Correct. >> > >> > However, if a record with a key less than 1000 gets updated or deleted, >> > there should be nothing to distribute as the row doesn't meet the >> > filter requirements and doesn't exist in the replicated database. But, >> > we get the following error: >> > >> > Error: The row was not found at the Subscriber when applying the >> > replicated >> > command. >> > >> > This sort of implies it is expecting to find a row with the sub 1000 >> > key in the replicated database. Why should this be? I can create a >> > new distribution agent profile to ignore this 20598 error, but our >> > customers are not comfortable with this. >> > >> > Anyone else experienced this? Any resolution? Am I missing something >> > obvious?? >> > >> > Regards >> > >> > Ian Hingley >> > Swift LG Ltd >> > >
Hi Hilary
Thanks for your reply. Can you tell me how I check: 'Or perhaps you are not enforcing a cascading delete or update for replication'? Sorry if this is mundane stuff for you; I really rate replication when it works, but struggle a bit when it goes wrong! Regards Ian [quoted text, click to view] Hilary Cotter wrote:
> There could be two issues, a deferred update where the update is replace by > an delete followed by an insert - which is unlikely as both should be > filtered out. Or perhaps you are not enforcing a cascading delete or update > for replication. Can you check for this? > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > <ian.hingley@swiftlg.com> wrote in message > news:1159960418.727872.65910@e3g2000cwe.googlegroups.com... > > Hi > > > > I have an issue where inserts follow row filter rules OK, but updates > > and deletions don't seem to. I'm using transactional replication. > > Let's assume we have a table called Customers with a simple row filter > > of > > > > SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 > > > > The snapshot and distribution only copies over records with a key > > greater than 1000. Correct. > > If a record with a key greater than 1000 is updated or deleted, the > > transactions are distributed and the same update or deletion appear on > > the replicated database. Correct. > > > > However, if a record with a key less than 1000 gets updated or deleted, > > there should be nothing to distribute as the row doesn't meet the > > filter requirements and doesn't exist in the replicated database. But, > > we get the following error: > > > > Error: The row was not found at the Subscriber when applying the > > replicated > > command. > > > > This sort of implies it is expecting to find a row with the sub 1000 > > key in the replicated database. Why should this be? I can create a > > new distribution agent profile to ignore this 20598 error, but our > > customers are not comfortable with this. > > > > Anyone else experienced this? Any resolution? Am I missing something > > obvious?? > > > > Regards > > > > Ian Hingley > > Swift LG Ltd > >
Hi Paul
Thanks for all that. I'll give it a go and report back here. Might take a couple of days. People somehow expect me to keep their replication working by magic, rather than spend chunks of quality time investigating the issues around it! Cheers Ian [quoted text, click to view] Paul Ibison wrote:
> Ian, > > if you run the query below, it'll correlate the article names and the > syncobject names. Using this, you'll be able to run sp_helptext on the > syncobject associated with the articles and determine that they are really > what you expect. > > SELECT syspublications.name AS Publication, sysobjects.name AS > SynObject, sysobjects_1.name AS ArticleName > FROM sysarticles INNER JOIN > sysobjects ON sysarticles.sync_objid = sysobjects.id > INNER JOIN > syspublications ON sysarticles.pubid = > syspublications.pubid INNER JOIN > sysobjects sysobjects_1 ON sysarticles.objid = > sysobjects_1.id > > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Hilary
You seem to be on to something here, though I'm not sure it's positive for me! Our Uniface application handles all the table relationships itself, so as far as SQL Server is concerned, there is no relationship between the tables. I've found the 'Enforce Relationships on Replication' check box (this was sounding really promising!), but unfortunately it is greyed out. Regards Ian [quoted text, click to view] Hilary Cotter wrote:
> In enterprise manager, right click on your table, and select properties, and > then relationships. You should see it there. > > In SSMS, right click on your table, select properties, right click on a > column and select relationships. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > <ian.hingley@swiftlg.com> wrote in message > news:1160044091.034295.233090@k70g2000cwa.googlegroups.com... > > Hi Hilary > > > > Thanks for your reply. > > Can you tell me how I check: > > > > 'Or perhaps you are not enforcing a cascading delete or update > > for replication'? > > > > Sorry if this is mundane stuff for you; I really rate replication when > > it works, but struggle a bit when it goes wrong! > > > > Regards > > > > Ian > > > > > > Hilary Cotter wrote: > >> There could be two issues, a deferred update where the update is replace > >> by > >> an delete followed by an insert - which is unlikely as both should be > >> filtered out. Or perhaps you are not enforcing a cascading delete or > >> update > >> for replication. Can you check for this? > >> > >> -- > >> Hilary Cotter > >> Director of Text Mining and Database Strategy > >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > >> > >> This posting is my own and doesn't necessarily represent RelevantNoise's > >> positions, strategies or opinions. > >> > >> 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 > >> > >> > >> > >> <ian.hingley@swiftlg.com> wrote in message > >> news:1159960418.727872.65910@e3g2000cwe.googlegroups.com... > >> > Hi > >> > > >> > I have an issue where inserts follow row filter rules OK, but updates > >> > and deletions don't seem to. I'm using transactional replication. > >> > Let's assume we have a table called Customers with a simple row filter > >> > of > >> > > >> > SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 > >> > > >> > The snapshot and distribution only copies over records with a key > >> > greater than 1000. Correct. > >> > If a record with a key greater than 1000 is updated or deleted, the > >> > transactions are distributed and the same update or deletion appear on > >> > the replicated database. Correct. > >> > > >> > However, if a record with a key less than 1000 gets updated or deleted, > >> > there should be nothing to distribute as the row doesn't meet the > >> > filter requirements and doesn't exist in the replicated database. But, > >> > we get the following error: > >> > > >> > Error: The row was not found at the Subscriber when applying the > >> > replicated > >> > command. > >> > > >> > This sort of implies it is expecting to find a row with the sub 1000 > >> > key in the replicated database. Why should this be? I can create a > >> > new distribution agent profile to ignore this 20598 error, but our > >> > customers are not comfortable with this. > >> > > >> > Anyone else experienced this? Any resolution? Am I missing something > >> > obvious?? > >> > > >> > Regards > >> > > >> > Ian Hingley > >> > Swift LG Ltd > >> > > >
Is this table published in other publications?
-- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] <ian.hingley@swiftlg.com> wrote in message news:1160054917.498303.22650@e3g2000cwe.googlegroups.com... > Hi Hilary > > You seem to be on to something here, though I'm not sure it's positive > for me! Our Uniface application handles all the table relationships > itself, so as far as SQL Server is concerned, there is no relationship > between the tables. I've found the 'Enforce Relationships on > Replication' check box (this was sounding really promising!), but > unfortunately it is greyed out. > > Regards > > Ian > > Hilary Cotter wrote: >> In enterprise manager, right click on your table, and select properties, >> and >> then relationships. You should see it there. >> >> In SSMS, right click on your table, select properties, right click on a >> column and select relationships. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> <ian.hingley@swiftlg.com> wrote in message >> news:1160044091.034295.233090@k70g2000cwa.googlegroups.com... >> > Hi Hilary >> > >> > Thanks for your reply. >> > Can you tell me how I check: >> > >> > 'Or perhaps you are not enforcing a cascading delete or update >> > for replication'? >> > >> > Sorry if this is mundane stuff for you; I really rate replication when >> > it works, but struggle a bit when it goes wrong! >> > >> > Regards >> > >> > Ian >> > >> > >> > Hilary Cotter wrote: >> >> There could be two issues, a deferred update where the update is >> >> replace >> >> by >> >> an delete followed by an insert - which is unlikely as both should be >> >> filtered out. Or perhaps you are not enforcing a cascading delete or >> >> update >> >> for replication. Can you check for this? >> >> >> >> -- >> >> Hilary Cotter >> >> Director of Text Mining and Database Strategy >> >> RelevantNOISE.Com - Dedicated to mining blogs for business >> >> intelligence. >> >> >> >> This posting is my own and doesn't necessarily represent >> >> RelevantNoise's >> >> positions, strategies or opinions. >> >> >> >> 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 >> >> >> >> >> >> >> >> <ian.hingley@swiftlg.com> wrote in message >> >> news:1159960418.727872.65910@e3g2000cwe.googlegroups.com... >> >> > Hi >> >> > >> >> > I have an issue where inserts follow row filter rules OK, but >> >> > updates >> >> > and deletions don't seem to. I'm using transactional replication. >> >> > Let's assume we have a table called Customers with a simple row >> >> > filter >> >> > of >> >> > >> >> > SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000 >> >> > >> >> > The snapshot and distribution only copies over records with a key >> >> > greater than 1000. Correct. >> >> > If a record with a key greater than 1000 is updated or deleted, the >> >> > transactions are distributed and the same update or deletion appear >> >> > on >> >> > the replicated database. Correct. >> >> > >> >> > However, if a record with a key less than 1000 gets updated or >> >> > deleted, >> >> > there should be nothing to distribute as the row doesn't meet the >> >> > filter requirements and doesn't exist in the replicated database. >> >> > But, >> >> > we get the following error: >> >> > >> >> > Error: The row was not found at the Subscriber when applying the >> >> > replicated >> >> > command. >> >> > >> >> > This sort of implies it is expecting to find a row with the sub 1000 >> >> > key in the replicated database. Why should this be? I can create a >> >> > new distribution agent profile to ignore this 20598 error, but our >> >> > customers are not comfortable with this. >> >> > >> >> > Anyone else experienced this? Any resolution? Am I missing >> >> > something >> >> > obvious?? >> >> > >> >> > Regards >> >> > >> >> > Ian Hingley >> >> > Swift LG Ltd >> >> > >> > >
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
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |