Collation conflicts are encountered when u try cross database query or cross server query or joining tables, because joining tables might have different collation settings. You must handle these query with specifying appropriate collation while joining to tables having different collation data. Let Replicate the issue with following example.
Create following tables with script given below.
CREATE TABLE Department_Branch
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](40) NULL,[NoOfBranch] TinyInt NOT NULL
)
GO
Insert Into Department_Branch Values('IT',40),('BIOLOGY',23),
('FINANCE',21),('MEDICAL',12),('TRANSPORT',5)
GoCREATE TABLE [dbo].[Department_Account](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](60) Collate SQL_Latin1_General_CP1_CI_AI NULL,
[TotalSalary] [decimal](18, 0) NULL,
[TotalEmployee] [int] NULL
)
GO
Insert Into Department_Account Values('IT',5700000,76),('BIOLOGY',8900000,290),
('FINANCE',4500000,900),('MEDICAL',560000,17),('TRANSPORT',700000,15)
Go
Here you have two tables Department_Branch with column DepartmentName (has default collation “SQL_Latin1_General_CP1_CI_AS” and Department_Account with column DepartmentName with Collation “SQL_Latin1_General_CP1_CI_AI”. Now Let join these to table and check the output.
Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A
Join Department_Account B On A.DepartmentName=B.DepartmentName
Go
Output :
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Now to resolve above error you must specify collation.
Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A Join Department_Account B
On A.DepartmentName=B.DepartmentName Collate SQL_Latin1_General_CP1_CI_AS
Go
Output :
Thank you very much for this usefull information! I really understand the topic now petrolhed
ReplyDeleteObrigado por compartilhar!
ReplyDeleteAjudou bastante! :D
Abraço
Muito obrigado, resolveu meu problema!
ReplyDeletehttps://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
HTGT2İ
https://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
URWVQ3