본 게시물은 개인적인 의견으로 작성되었으니 절대적인 정보가 아닐 수 있습니다. 참고만 하시고 궁금한 사항이 있으시면 연락주세요.

티스토리 뷰



-- SQL Server Collation Checking

select serverproperty('collation')

/* Korean_Wansung_CI_AS */


-- Create database with different collation

CREATE DATABASE [DB_Not_Contained]

CONTAINMENT = NONE

COLLATE French_CS_AI

GO


-- Create 2 tables with 1 being a temp table so it goes to tempdb and uses the server collation and not the database collation

USE [DB_Not_Contained]

GO

CREATE TABLE [DemoCollation]

(DemoCollationNM VARCHAR(100))

GO

CREATE TABLE #DemoCollation

(DemoCollationNM VARCHAR(100))



-- Insert records into both tables

INSERT dbo.DemoCollation

(DemoCollationNM)

VALUES ('Test Join');

INSERT #DemoCollation

(DemoCollationNM)

VALUES ('Test Join');


-- Now query and try and join both tables having 2 different collations

SELECT p.DemoCollationNM

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON p.DemoCollationNM = d.DemoCollationNM

/*

메시지 468, 수준 16, 상태 9, 줄 34

equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "French_CS_AI" 간의 데이터 정렬 충돌을 해결할 수 없습니다.

*/


-- 해결책은 무엇일까?

-- 각 오브젝트의 Collation 을 확인해보자

use [DB_Not_Contained]
go
sp_help DemoCollation


use tempdb
go
sp_help #DemoCollation


-- 해결해 보자


-- 방법. 1

use [DB_Not_Contained]

go

--Now query and try and join both tables having 2 different collations

-- "COLLATE DATABASE_DEFAULT" 옵션을 추가하면 tempdb 의 오브젝트가 현재 데이터베이스의 Collate 를 따라간다.

SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM COLLATE DATABASE_DEFAULT

-- 방법. 2

SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM COLLATE French_CS_AI

-- 방법. 3

drop table #DemoCollation

CREATE TABLE #DemoCollation 

(DemoCollationNM VARCHAR(100) COLLATE French_CS_AI)

INSERT #DemoCollation (DemoCollationNM) VALUES ('Test Join');


SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM


[참고문서]
SQL SERVER – Fix – Error: Msg 468, Level 16, State 9, Line 1
http://blog.sqlauthority.com/2015/07/20/sql-server-fix-error-msg-468-level-16-state-9-line-1/



댓글
댓글쓰기 폼
1 ··· 253 254 255 256 257 258 259 260 261 ··· 337