Search

SQLSERVER 2012 - CONTAINED DATABASES & Collation problem by Marc Snoeys

Resolved
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)
3
User(s) can reproduce this bug
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 (expand)

Product Language

English

Version

SQL Server 2012 - Enterprise Edition

Category

SQL Engine

Operating System

Windows Server 2008 R2 Enterprise

Operating System Language

English

Steps to Reproduce

USE [master]
GO

CREATE DATABASE [test] CONTAINMENT = PARTIAL
GO

USE [test]
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
;

Actual Results

Error message:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.

Expected Results

OUTPUT FROM OUTPUT-clause: INSERT ADDED case NULL

Record inserted in TEST_CASE

Platform

 

Virtualization

 
File Attachments
0 attachments
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.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement