Thumper…

May 29, 2015

SQL Server 2008 Replication Problems

A few days ago I changed a UDF on one of our SQL servers (2008 R2) and in doing so discovered we have different collations on one of our servers involved with replication.
This was the error we got:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Trying to change the collation of the database resulted in errors because all these objects were involved in replication. (Ensure you set the database to single use if you are trying to change the collation).

The object ‘repl_identity_range_6C3C888C_5041_44EB_B890_5C54904132AD’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

We tried dropping that object from the snapshot, regenerating it and then replicating again.
We were still getting the same error.
Checked the logs (we have verbose logging = 2) and log our replication output to a text file.

It was definitely the original object causing the problem, despite removing it from the snapshot.
Finally we dug into the schema tables using the following query:

SELECT TOP 1000 [pubid],[artid],[schemaversion],[schemaguid],[schematype],[schematext],[schemastatus],[schemasubtype]
FROM [pigfmcor].[dbo].[sysmergeschemachange]
where schematext like ‘%monthly%’

Where “Monthly” was part of the name of the object initially changed.
We deleted those rows from that table (changing the schemestatus value did not work) at the publisher AND the subscriber.

Bingo! Our replication went through without a problem!

Advertisements

Blog at WordPress.com.