Here is one possible explanation as to what is going on:
In the contained database scenario $action is actually collated using the catalog default, i.e. Latin1_General_100_CI_AS_WS_KS_SC, per the table under the section Contained Databases
in this article
. Try adding COLLATE DATABASE_DEFAULT
to collate $action in the CASE expression.
It strikes me as very odd that changing the containment type of a database results in different behavior in code: in this case the $action
But this is just a personal opinion (however a few colleagues do agree)
I did a kind of an extension to this test-case:
- Database [test] in CONTAINMENT TYPE=NONE
- Create a stored procedure with the MERGE-statement above.
- Try to change the CONTAINMENT TYPE=PARTIAL
That resulted in following error, which is basically the same as in the first test-case.
Msg 468, Level 16, State 9, Procedure sp_TEST_CASE, Line 19
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.
Msg 12813, Level 16, State 2, Line 2
Errors were encountered in the procedure 'dbo.sp_TEST_CASE' during compilation of the object. Either the containment option of the database 'test' was changed, or this object was present in model db and the user tried to create a new contained database.
Msg 12836, Level 16, State 1, Line 2
ALTER DATABASE statement failed. The containment option of the database 'test' could not be altered because compilation errors were encountered during validation of SQL modules. See previous errors.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Apparently there is a view where one can query for possible problems when changing to a CONTAINED database: SYS.DM_DB_UNCONTAINED_ENTITIES
But a query on this view does not show this particular stored procedure.
All in all: I guess I'm forced to use the COLLATE-clause ...
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"