USE [master]
GO

ALTER DATABASE [test] SET CONTAINMENT = PARTIAL WITH NO_WAIT -- WILL RESULT IN ERROR
-- ALTER DATABASE [test] SET CONTAINMENT = NONE WITH NO_WAIT -- WILL WORK
GO

USE [test]
GO

DROP TABLE TEST_CASE
GO

CREATE TABLE TEST_CASE
	(	name	varchar(10)		NOT NULL
	,	CONSTRAINT PK_LOG_FILTER_PK PRIMARY KEY CLUSTERED (name)
	)
GO

;
MERGE INTO TEST_CASE	Target
USING	(
		SELECT	'case'	AS name
		)	Source
ON	( Target.name = Source.name )
WHEN NOT MATCHED BY TARGET	THEN	INSERT ( name ) VALUES ( Source.name )
WHEN NOt MATCHED BY SOURCE	THEN	DELETE
OUTPUT	$action
	,	CASE
			WHEN $action = 'INSERT' THEN 'ADDED'
			WHEN $action = 'DELETE' THEN 'REMOVED'
		END ACTION
	,	inserted.name
	,	deleted.name
;

ON	( Target.name = Source.name )

ON	( Target.name COLLATE Latin1_General_CI_AS = Source.name COLLATE Latin1_General_CI_AS )

$action	ACTION	name	name
INSERT	ADDED	case	NULL

CASE
WHEN $action COLLATE Latin1_General_CI_AS = 'INSERT' COLLATE SQL_Latin1_General_Cp437_BIN THEN 'ADDED'
WHEN $action = 'DELETE' THEN 'REMOVED'
END ACTION

WHEN $action COLLATE SQL_Latin1_General_CP1_CI_AS = 'DELETE' THEN 'REMOVED'

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.

WHEN $action COLLATE DATABASE_DEFAULT = 'DELETE' THEN 'REMOVED'