SQLSERVER 2012 - CONTAINED DATABASES & Collation problem - by Marc Snoeys

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 772361 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 11/27/2012 6:42:25 AM
Access Restriction Public


When using a MERGE-statement and validating the $action field in the OUTPUT-clause, we are receiving an error message about collaction conflict when using a database with CONTAINMENT=PARTIAL

BUT : running the same code (see Steps to reproduce) with a database with CONTAINMENT=NONE does not issue the error message!

Sign in to post a comment.
Posted by Microsoft on 3/5/2013 at 11:50 AM
Hello Marc,
We have now fixed this bug in the next major version of SQL Server. Thanks for reporting the issue.

Umachandar, SQL Programmability Team
Posted by Microsoft on 12/5/2012 at 2:10 PM
Hello Marc,
Thanks for reporting the issue. The problem is due to the $action column metadata which is incompatible with the literal collation (string values 'INSERT' or 'DELETE'). This should work without requiring the collate clause. We will take a look at this.

Umachandar, SQL Programmability Team
Posted by Werner Geuens on 11/28/2012 at 2:39 AM

If we agree that this feature (merge statement with OUTPUT $action) cannot be used in a containted database,
I would expect the database_uncontained_usage extended event to fire when evaluating this database.
However, this is not the case!

Also, I would expect a stored procedure that uses the merge statement with OUTPUT $action to appear in the result of this query:

UncType = ue.feature_type_name,
UncName = ue.feature_name,
RefType = o.type_desc,
RefName = o.name,
Stmt = ue.statement_type,
Line = ue.statement_line_number,
StartPos = ue.statement_offset_begin,
EndPos = ue.statement_offset_end
sys.dm_db_uncontained_entities AS ue
INNER JOIN sys.objects AS o ON o.object_id = ue.major_id
WHERE ue.feature_type_name NOT LIKE 'System%'

But again, it doesn't...
So I agree with MarcSn that this looks like a bug.

Kind regards,