Hello,
With hosting in Azure databases, we run into Containment and Collation issues from time to time. I just ran into an issue again where I couldn't edit any of the integration Endpoints from the backend. When trying to make a change, it throw this error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.
The database and all tables and columns are all using SQL_Latin1_General_CP1_CI_AS. I was able to narrow down and get a query to repro it. This is a subset of the query used to update Endpoint.
MERGE [Endpoint] WITH (SERIALIZABLE) AS T USING (VALUES (0, 1, 0) ) AS S
(SourceIndex, EndpointId, EndpointUseInLiveIntegration) ON T.EndpointId = S.EndpointId
WHEN MATCHED THEN UPDATE SET EndpointUseInLiveIntegration = 0
OUTPUT CASE WHEN $action = 'INSERT' THEN 'something' ELSE 'something else' END AS TestVal;
It's the $action that is the problem. That uses a different collation than the database and columns, causing the collation clash. Here's a good forum that discusses it: https://www.sqlservercentral.com/forums/topic/contained-database-or-not
For now, I'll work around it by turning off containment on this particular database (it's a copy from Azure, on a local SQL server). But, it would be nice to have the platform handle for this situation.
The solution is to add this after every $action
MERGE [Endpoint] WITH (SERIALIZABLE) AS T USING (VALUES (0, 1, 0) ) AS S
(SourceIndex, EndpointId, EndpointUseInLiveIntegration) ON T.EndpointId = S.EndpointId
WHEN MATCHED THEN UPDATE SET EndpointUseInLiveIntegration = 0
OUTPUT CASE WHEN $action COLLATE DATABASE_DEFAULT = 'INSERT' THEN 'something' ELSE 'something else' END AS TestVal;
If the "COLLATE DATABASE_DEFAULT" is added to every MERGE command after the $action variable, it will gracefully handle contained databases.
