On our development server, we have a database server with collation: COLLATE SQL_Latin1_General_CP1_CI_AS.
After deploying our solution on a server and that database server has collation: COLLATE SQL_Latin1_General_CI_AS
That means if we have a query:
SELECT ('text' + 'abc') AS 'result'
I got this problem:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
So I tried this: ALTER DATABASE [mydb] COLLATE SQL_Latin1_General_CP1_CI_AS
then I check the property of the myDB, the collate is changed to: SQL_Latin1_General_CI_CS_AS but I still get the same error.
Other topics suggest to reinstall the database. But that is not the case we will lose all of the data.
Any suggestion is very appreciated!
Thanks in advance.
In a nutshell, it's not enough to alter the database, as that only affects new objects that are created going forward. You also have to change all of the existing columns. This Microsoft support article should have all the details you'll need.
How to transfer a database from one collation to another collation in SQL Server