Error "Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'" when applying snapshot to the Subscriber database

When setting up merge replication, you may get the following error:

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'

while the initial snapshot is applied to the Subscriber database.

How can data that violates no primary key constraints at the Publisher cause such an error at the Subscriber? This can be caused by different collations used by the Publisher and Subscriber (at the database, table or column level).

If the Publisher database has a case-sensitive collation (e.g. SQL_Latin1_General_CP1_CS_AS ), the following data is correct (columns Type and Code are a primary key):

Type  Code  Column1
024   N     ...
024   n     ...
024   g     ...

However, an attempt to insert these rows into a table with case-insensitive collation (e.g. Latin1_General_CI_AI) will cause a primary key violation.

Filter results in SQL Server Management Studio may be case-sensitive

If you are working with a database that has a case-sensitive collation (e.g. Latin1_General_CS_AS), the filter function in SQL Server Management Studio will use this setting and the results will be case-sensitive.

Filter showing tables with Bitmap in the name:

Filter showing tables with bitmap in the name:

If this database had a case-insensitive collation, filter results for Bitmap and bitmap would be identical and would contain all four tables.