You find you are querying a vendor supported database where you're not allowed to make any collation changes, and which has a weird collation setup:
- default DB collation that matches your server and other databases
- all character related columns are explicitly a different collation that does not match your DB or server collation
Rather than change complex queries to collate to the correct columns, covering many different tables, a way was needed to generate an intermediate view that is simply a select to all of that TargetDB's tables, autocorrecting the collation to what you need.
So, for example, you have a salesorder table where the First and Last Name are Latin1_General_100_CI_AS but the server and other databases are SQL_Latin1_CP1_CI_AS. The script I have created will generate a view similar to this:
CREATE VIEW [dbo].[vw_sales_salesorders] AS SELECT saleID, firstName COLLATE SQL_Latin1_CP1_CI_AS firstName, -- TARGETDB is Latin1_General_100_CI_AS lastName COLLATE SQL_Latin1_CP1_CI_AS LastName, -- TARGETDB is Latin1_General_100_CI_AS salesAmount FROM [TARGETDB].[sales].[salesorders]
This can be run manually, or in our case, as we're not sure when that schema will be changed, schedule it as a job to run daily early morning.