Home Dashboard Directory Help
Search

SQLSERVER 2012 - CONTAINED DATABASES & Collation problem by Marc Snoeys


Status: 

Closed
 as Fixed Help for as Fixed


11
0
Sign in
to vote
Type: Bug
ID: 772361
Opened: 11/27/2012 6:42:25 AM
Access Restriction: Public
2
Workaround(s)
view
3
User(s) can reproduce this bug

Description

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!



Details
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
Hi,



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:

SELECT
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
FROM
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,

Werner
Sign in to post a workaround.
Posted by Orlando Colamatteo on 12/3/2012 at 2:33 PM
Using the collation-constant DATABASE_DEFAULT will create portable code for use when the database is not contained or when it is partially contained, for example:

WHEN $action COLLATE DATABASE_DEFAULT = 'DELETE' THEN 'REMOVED'
Posted by Bob Beauchemin on 11/27/2012 at 8:45 AM
The workaround for this is to specify a collation (via the COLLATE clause) on either the $action or the string literal. ANY collation will work on either of the two items.