0

I've been getting a replication error for some time now between my Publisher and Subscriber databases (SQL 2016). The error is the classic:

"42000 The row was not found at the Subscriber when applying the replicated UPDATE command for Table '[dbo].[table_name]' with Primary Key(s): [field1] = ÊÀÏðÀËÎÀ, [field2] = 15501 , [field3] = TS001 20598"

Table definition (both Pub and Sub):
field1 = PK CHAR(8) NOT NULL
field2 = PK CHAR(8) NOT NULL
field3 = PK CHAR(8) NOT NULL

If I query for that record in the Publication database using all the Primary Key values I get zero records returned. The same is true in the Subscriber database. The issue seems to be with the 'field1' value as the other two key values find matches in both publication and subscription dbs, but field1 is the unique identifier. Values in the Publication table for Field1 (when matching field2 and field3) are:
КАПрАЛОА
±КЛЇрОВА

I'm wondering if there is something going on here to do with collation or codesets as the Publication database uses a collation of Cyrillic_General_BIN (vendor specified!) but the hosting SQL instance and the Subscriber database are both using Latin1_General_CI_AS. The publication properties are set to 'Copy collation = True' so it should be recognising the collation and passing it through. The Publication server is also configured for a different language, hence the thought about the codeset.

If I create a new snapshot and publish that then all is well for a few days and then the issue happens again, but with different key values. I suspect that it's happening when the user changes the data. Other replicated tables do not seem to be affected in the same way as sometimes replication is successful (presumably when no changes tare made to table_name).

I've also tried adding verbose logging to the distribution job but the resulting file shows the same error as reported above and no more.

The fact that the field1 value does not exist in the Publication database is suspicious.

Any ideas where I can go next to resolve this issue?

Thanks,

Mike

7
  • I've recently ran into the same exact error when replicating from a Publisher who's collation is different than my Subscriber's, and I actually specifically setup my Publication Articles not to copy the collation. But in my case this actually had nothing to do with the collation and rather had to do with my dedicated User for replication not having the appropriate Row-Level Security permissions to see the data on my Subscription database. Any chance you are using Row-Level Security or some other feature that could be potentially preventing your replication account from seeing the data?
    – J.D.
    Sep 23 '21 at 11:44
  • I'm not using row-level security (vendor in the dark ages). The data is 'seen' by the distributer but it somehow gets a different value for field1 to what I see if I query the table directly.
    – renMike
    Sep 23 '21 at 13:19
  • Having seen the data in the subscription database it then tries to update the matching record in the publication database but, because the field1 has a different value to what is in both the subscription and publication dbs it is unable to complete the update and errors.
    – renMike
    Sep 23 '21 at 13:38
  • Sorry, i'm confusing the matter now as I transposed subscription and publication in my last comment. The data is seen in the publication db but is then somehow changed.
    – renMike
    Sep 23 '21 at 13:41
  • If I dig a bit deeper I can see that the Distribution.dbo.sp_browsereplcmds is returning '????????' for my field1 value (it's a char 8) rather than either of the values in my initial question. This is somewhat understandable since it's a CHAR field and unicode text. If the field were an NCHAR then maybe the sp_browsereplcmds would show the correct vales. This could be an indication of something going wrong since I can't verify the value of field1 in the Distribution database.
    – renMike
    Sep 23 '21 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.