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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: