We have set up Transactional Replication in which Publisher and Subscriber side databases are put under the same SQL Servers. Publisher database collation along with column-level collation is SQL_Scandinavian_Pref_CP850_CI_AS, whereas the subscriber side is Finnish_Swedish_CI_AS. SQL Server collation is also SQL_Scandinavian_Pref_CP850_CI_AS. When I am trying to replicate a varchar column having data with special characters like 'åÅäÄöÖ', then it's getting delivered as '†„Ž”™' on the subscriber side, which is not acceptable.
Can you guys please suggest any solution to get the correct data on the subscriber side? Also, if this bug can't be fixed then please suggest any alternate architecture to deliver the data between databases with different collations? Please suggest.
This question is a duplicate of another one, which I posted on stackexchange.com. I have posted the answer there as well. Please find the link below to that post: https://dba.stackexchange.com/questions/278640/changing-collation-of-sql-database-in-production-environment-to-minimize-downtim/278933#278933