JohnEver get an error message that goes something like this:
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin_General_CI_AS”…
Generally, the two columns that are being used have different collation options. Collation “…specifies the bit patterns that represent each character in a dataset. Collations also determine the rules that sort and compare data.” (source). So if you are having errors with comparing fields, check the collation between the fields.
I’ve created a simple little database project to display an example:
The image above shows two database projects; one as the source and the other as target. The goal is to move data from CollateSourceDB.dbo.tblPerson, stage only changes in CollateTargetDB.stg.tblPerson, and merge the changes into CollateTargetDB.dbo.tblPerson. I have already deployed the source database to my local as Local_CollationSource. Here is the data in Local_CollationSource.dbo.tblPerson:
As I try to deploy CollateTargetDB to Local_CollationTarget, we get an error in the Data Tools Operations window:
Viewing the log from the message output:
We can see we’re having a collation conflict issue with the EXCEPT operation in in the stored procedure spGetPerson. Taking a look at the procedure:
Confirming nothing suspicious is going on with the code, we should go to the column level to see the collations set:
/* Code from https://stackoverflow.com/questions/1607560/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and */
We can confirm FirstNameDenorm, MiddleNameDenorm, and LastNameDenorm have different collations between the source and target for dbo.tblPerson. One solution is to update the columns in the target to match the source:
Now the deploy works, we validate the collations match:
Running the procedures:
This was an oversimplified way of how I found the issue in my other projects. Hopefully helps you.
Happy coding!
