Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

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)
Go
 
CREATE 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 :