Post reply

Replication to another server fails - possibly collation

  • October 11, 2021 at 1:12 pm

    #3939457

    Hello,

    I have a SQL 2016 SP2 instance running on server A. The collation of this instance is Latin1_General_CI_AS. The instance hosts a user database used for transactional replication (push) as the publication (dbP) and this database has a collation of Cyrillic_General_BIN. This replicates a table (dbP.tbl1) using a key containing a CHAR(10) field and the article has the "Copy collation" value set to True. The distributor database (dbD) also exists on this same instance and has a collation of Latin1-General_CI_AS.

    I have a second SQL 2016 SP2 instance running on server B. The collation of this instance is Latin1_General_CI_AS. This instance hosts a second user database as the subscriber (dbS) and has a collation of Latin1_General_CI_AS.

    When I initiate a new snapshot and distribute dbP.tbl1 from dbP via dbD to dbS it all works fine. However, when I amend a record in dbP.tbl1 and allow the Log Reader to update dbD and then the Distributor to UPDATE dbS I get an error:

    2021-10-11 12:23:18.956 Agent message code 20598. The row was not found at the Subscriber when applying the replicated UPDATE command for Table '[dbo].[tbl1]' with Primary Key(s): [key1] = ¦--»­+-+, [key2] = 15500 

    2021-10-11 12:23:19.003 Category:COMMAND

    Source: Failed Command

    Number:

    Message: if @@trancount > 0 rollback tran

    2021-10-11 12:23:19.003 Category:NULL

    Source: Microsoft SQL Server Native Client 11.0

    Number: 20598

    Message: The row was not found at the Subscriber when applying the replicated UPDATE command for Table '[dbo].[tbl1]' with Primary Key(s): [key1] = ¦--»­+-+, [key2] = 15500

    I suspect that the different collations on the various databases is causing my issue and that I'll probably have to alter the UPDATE statement to allow for NCHARs but I'm not sure if I'm heading in the right direction. Can anyone offer any advice on this matter?

    Thanks,

    Mike

  • October 12, 2021 at 1:04 pm

    #3939778

    Looks like I needed to amend the collation of the subscriber db (dbS) to match that of the publisher (dbP) as after I did that and reinitialised the snapshot the transactions then flowed without error.

    This hasn't yet gone into production so I don't know if this is the final solution, but it's certainly resolved the issue in the QA environment.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply