Cannot resolve collation conflict for equal to operation.
In MS SQL SERVER, the collation can be set at the column level. When compared to 2 different collation columns in the query, this error comes up.
SELECT ID FROM ItemsTable INNER JOIN AccountsTable WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col
If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.
To resolve the collation conflict add the following keywords around “=” operator.
SELECT ID FROM ItemsTable INNER JOIN AccountsTable WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT = AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT
Watch this quick video for resolution:
Collation can affect the following areas:
- Where clauses
- Join predicates
- Functions
- Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)
Checkout following video how collation can change the order of the column:
Just like collation, there is another problem with SQL Server and that is related to Parameter Sniffing, I have written a detailed blog post about how the parameter sniffing can be resolved in SQL Server.
Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.
- SQL SERVER – Parameter Sniffing Simplest Example
In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure. - SQL SERVER – Parameter Sniffing and Local Variable in SP
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. - SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed. - SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing. - SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance. - Performance and Recompiling Query – Summary
This post summarizes the entire series of parameter sniffing, performance and recompiling query.
Reference: Pinal Dave (https://blog.sqlauthority.com)
381 Comments. Leave new
Search so many places – this was the easiest answer… Thank you
Thank you very much!
This solved my problem. Thank you very much.
SQL scripts that helped me a lot thanks
Thank you very very much, you saved my tens of hours
THANKS FOR SOLUTION
Thankyou !
Thanks! Its too good.
THANK YOU!! worked perfectly!
Thanks..!!! Perfect solution.
Many Thanks my friend.
Many many thanks! … ON a 3am cutover … and this enabled our script to run.
Thanks !!! Its a quick and effective solution.
i have a procedure with create temp table using with common table expression . then i insert record into temp table. after that i update one table. procedure contains so many parameters. Same procedure call from different form from vb.net not given error but in one form it gives error below.
Cannot resolve the collation conflict between “Japanese_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Please give solution.
Pinal, how do i resolve this error in the following update statement
update DW_Store set Appts = (select count(AppointmentID) from sdsdb.dbo.Appointments inner join sdsdb.dbo.stores on sdsdb.dbo.Appointments.StoreID = sdsdb.dbo.stores.StoreID where sdsdb.dbo.stores.StoreReference = bi.dbo.DW_Store.Store and sdsdb.dbo.Appointments.AppointmentStartDateTime collate SQL_Latin1_General_CP1_CI_AS = bi.dbo.DW_Store.Date collate Latin1_General_CI_AS)
this helped me a lot, thanks!
ALTER PROCEDURE [dbo].[usp_Rep_CMSReporting_CIRelationshipViewnew]
@CINAME VARCHAR(Max)
AS
BEGIN
CREATE TABLE #HIERARCHY(PARENT VARCHAR(MAX),
CHILD VARCHAR(MAX),
PARENT1 VARCHAR(MAX),
CHILD1 VARCHAR(MAX))
INSERT INTO #HIERARCHY (PARENT,CHILD,PARENT1,CHILD1)
SELECT CI.CIName + ‘ (‘ + ciClient.CIName + ‘) ‘ + ‘ ‘ + CI.CIDescription As PARENT ,CI1.CIName + ‘ (‘ + ciClient1.CIName + ‘) ‘ + ‘ ‘ + CI1.CIDescription AS CHILD,ci.CIName as parent1,ci1.CIName as child1 FROM AssociatedCI ASSOC
INNER JOIN CIMASTER CI ON CI.ID = ASSOC.CIMasterID
JOIN CIOrganisationMap ciom on CI.ID = ciom.CIID
JOIN CIMaster ciClient on ciom.OrganisationID = ciClient.ID
INNER JOIN CITYPE CT ON CI.CITypeID = CT.CITypeID
INNER JOIN CIMaster CI1 ON CI1.ID = ASSOC.AssociatedCIID
JOIN CIOrganisationMap ciom1 on CI1.ID = ciom1.CIID
JOIN CIMaster ciClient1 on ciom.OrganisationID = ciClient1.ID
INNER JOIN CITYPE CT1 ON CI1.CITypeID = CT1.CITypeID
WHERE ciClient.ID NOT IN(81) and ciClient1.ID NOT in (81)
declare @t table (parent varchar(max), child varchar(max), lev int, fullpath varchar(max),parent1 varchar(max), child1 varchar(max))
declare @lev int
set @lev = 0
–Get Root node(s)
insert @t (parent, child, lev, fullpath,parent1,child1)
select distinct null, p.parent, @lev, p.parent,null,p.PARENT1
from #HIERARCHY p
left join #HIERARCHY c on c.child = p.parent
where c.child is null
while @@rowcount > 0
begin
set @lev = @lev + 1
–Get all children of current level
insert @t (parent, child, lev, fullpath,parent1,child1)
select h.parent, h.child, @lev, t.fullpath + ‘ –> ‘ + h.child,h.PARENT1,h.CHILD1
from @t t
join #HIERARCHY h on h.parent = t.child and t.lev = @lev-1
–make sure a circular reference doesn’t put is in an infinate loop
left join @t x on x.parent = h.parent and x.child = h.child
where x.parent is null
end
–print ‘helper table’
–select * from @t order by fullpath
select Distinct replicate( ‘–>’, lev)+ ‘ ‘ + child AS ‘Relationship View’,fullpath,lev,parent1,child1 from @t
where parent1 like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’ or child like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’ or fullpath like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’
order by fullpath
–order by lev,child
drop table #hierarchy
END
Msg 468, Level 16, State 9, Procedure usp_Rep_CMSReporting_CIRelationshipViewnew, Line 43
Cannot resolve the collation conflict between “Latin1_General_CI_AS_KS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Hello,
I am looking for the resolution/clue for the error message I have been receiving since last 2-3 days.
Here is the scenario:
I have an .NET application where I am using SQL Server 2012 with Advanced Services x86 for database management. My development PCs have Windows 7 OS (one with 32-bit and other with 64-bit). The application is running as expected. I added one more PC with Windows 8 OS (64-bit) for testing purpose. I installed SQL Server 2012 with Advanced Services x64 on that PC. When I restored the database from Win7 pc to Win8 pc it got restored successfully in one go. On the database side, on both Win7 and Win8 pc, the database collation is set to the default type “SQL_Latin1_General_CP1_CI_AS”
Since past 2-3 days the application, when run on the Win8 pc, is giving me error for a particular stored procedure Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation. It happens only on Win8 pc. I have not used any collation directives while using joins or comparing column values. I have already rechecked and reset the collation type on both the PCs but the error still persists on the win8 pc.
I am wondering why is this happening only on Win8 pc?
Could this issue be related with SQL Server version on Win8 pc? If yes, what could be the resolution to the issue?
Please share your valuable thoughts.
Thanks in advance
How is the MS documentation wrong? https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017 )
And then people on StackOverflow quote it?
I have to come here to get the right answer. Thanks for this article, and stay awesome, my friend.
Thanks Ben!
Hello,
I am looking for the resolution have an .VISUAL 6.0 application where I am using SQL Server 7.0 and try execute this script:
CREATE TABLE [dbo].[LogEventos] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Usuario] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Fecha] [datetime] NULL ,
[Evento] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Cuenta] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[CodApodera] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Dni] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Volumen] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Imagen] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,
[Motivo] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
but this message:
INCORRECT SYNTAX NEAR COLLATE.
Please help
Thanks
I don’t have SQL 7.0 to test and find solution. Sorry.