SQL Server Conflict Conflict

Question

Transferring data from one SQL server to another, but when Schema is compared and synchronized, the following error occurs. We use redgate SQL for comparison.

Unable to resolve collision conflict for equal operation

The base SQL server is SQL_Latin1_General_CP1_CI_AS, and the destination server is Latin1_General_CI_AS

Answer 1

SQL Compare has the ability to ignore mappings. Look under the Options tab in your comparison project configuration.

Answer 2

It looks like the sorting settings for the server are different.

How do you transfer data, do you perform database recovery on your new platform?

In any case, you need to make sure that your new environment uses the same sorting as your original environment.

Hope this makes sense, let me know if you need more help.

Answer 3

Are you a problem with SQL Compare utlity or are you worried that different server sorts will lead to problems?

You can change the setting of the target server according to the base server

If this is not possible, do a database mapping on each server, and then your only real problem is likely to be any temporary tables that you create (they will have a default mapping matching the server / TEMPDB), and while you explicitly create temporary table (i.e. do not create it with SELECT * INTO #TEMP FROM MyTable) and explicitly assign a mapping to any varchar / text columns, you should be fine

Answer 4

As I overcome this, I need to generate scripts using SQL Compare, and then remove (or replace) the Collation code. This is relatively quick and easy to do, and finally, I manually apply the scripts to the target server / database.

Answer 5

Ignore Mappings will definitely not work for the above reason. The problem occurs when wrapping objects, such as views and stored procedures, that use JOIN clauses in text fields with different sorts.

If someone changes the default setting on the server, and the column on the other side of the JOIN uses a specific sort, you caused this problem. And this will happen in SQL Compare, as well as if you just manually launched the object in SSMS and moved it yourself.

There are two ways to fix it - you can specify the COLLATE clause in the connection and explicitly specify the sort you want to use, or change the default setting for the destination database in accordance with the source code.

I am afraid there is no SQL Compare "magic bullet" for this.