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
Hi!!!
How can i use the COLLATE DATABASE_DEFAULT in this update
update dialer_campSource set iZonaHoraria = hh.tz_standard, iZonaHoraria_verano = hh.tz_daylight
from dialer_campSource cs inner join dialer_ccTimeZoneAreaMex hh
on
( len(cs.tel1) = 8 and @lada = hh.area and len(hh.area) = 2 )
or
( len(cs.tel1) = 7 and @lada = hh.area and len(hh.area) = 3 )
or
( len(cs.tel1) >= 10 and left(right(cs.tel1, 10), 3) = hh.area and len(hh.area) = 3 )
or
( len(cs.tel1) >= 10 and left(right(cs.tel1, 10), 2) = hh.area and len(hh.area) = 2 )
inner join inserted i
on cs.callout_id = i.callout_id
Thanks a lot. Good idea..
Great post brother, keep up the good work.
good!
Thanks a lot!!!
How can I use the Collate clause if I am matching say –
Select accountID from Table1
where AccountID in (select accountID from table2)
Very helpful, thank you!
Anu this worked fine for me:
“WHERE accountID COLLATE DATABASE_DEFAULT IN
(SELECT accountID COLLATE DATABASE_DEFAULT
FROM table2)”
Hi,
I am getting error ‘Cannot resolve collation conflict for equal to operation.’ while running below procedure.
I dont have much idea about sqlserver.
CREATE PROCEDURE [dbo].[SP_GETEXCHANGERATEFRMAMEX] AS
set xact_abort on
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
–BEGIN DISTRIBUTED TRANSACTION
DECLARE @DEFAULTCUR NUMERIC
SELECT @DEFAULTCUR=DEF_CUR_ID FROM OR_DEFAULTS
DECLARE @EX_CUR_ID NUMERIC
DECLARE @EX_TT_ID NUMERIC
DECLARE @EX_RATE NUMERIC(18,8)
DECLARE @EX_UpdatedBy NVARCHAR(50)
DECLARE @EX_UpdatedOn DATETIME
DECLARE EXCURSOR CURSOR FOR
SELECT V_EXC_FRM_CCY AS EX_CUR_ID, TT_ID AS EX_TT_ID, N_EXC_SLS_DFL AS EX_RATE, ‘AMEX’ AS EX_UpdatedBy,
D_UPD_DAT AS EX_UpdatedOn FROM AMEXORA..AMEXHO.TBL_CCY_EXC_MST INNER JOIN OR_TRANSACTIONTYPE
on v_exc_frm_ccy_typ=TT_AMEX_REF WHERE CONVERT(NVARCHAR(10),V_EXC_TO_CCY) + ” + CONVERT(NVARCHAR(10),V_EXC_TO_CCY_TYP)
NOT IN (SELECT CONVERT(NVARCHAR(10),EX_CUR_ID) + ” + CONVERT(NVARCHAR(10),TT_AMEX_REF) FROM OR_EXCHRATE INNER JOIN
OR_TRANSACTIONTYPE ON TT_ID=EX_TT_ID WHERE EX_CUR_ID=@DEFAULTCUR) AND V_EXC_TO_CCY = @DEFAULTCUR and v_exc_to_ccy_typ = 0 AND V_EXC_RT_OPR = ‘M’
order by V_EXC_FRM_CCY, TT_ID
FOR READ ONLY
OPEN EXCURSOR
FETCH NEXT FROM EXCURSOR INTO @EX_CUR_ID, @EX_TT_ID, @EX_RATE, @EX_UpdatedBy, @EX_UpdatedOn
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT * FROM OR_EXCHRATE WHERE EX_CUR_ID=@EX_CUR_ID AND EX_TT_ID=@EX_TT_ID)
UPDATE OR_EXCHRATE SET EX_RATE=@EX_RATE, EX_UpdatedBy=@EX_UpdatedBy, EX_UpdatedOn=@EX_UpdatedOn WHERE EX_CUR_ID=@EX_CUR_ID AND EX_TT_ID=@EX_TT_ID
ELSE
INSERT INTO OR_EXCHRATE (EX_CUR_ID, EX_TT_ID, EX_RATE, EX_UpdatedBy, EX_UpdatedOn) VALUES (@EX_CUR_ID, @EX_TT_ID, @EX_RATE, @EX_UpdatedBy, @EX_UpdatedOn)
if @@rowcount>0
print ‘Updated new Exchange Rate for currency ‘ + convert(nvarchar(10),@EX_CUR_ID) + ‘ – ‘ + convert(nvarchar(50),dateadd(Hour,4,getutcdate()))
FETCH NEXT FROM EXCURSOR INTO @EX_CUR_ID, @EX_TT_ID, @EX_RATE, @EX_UpdatedBy, @EX_UpdatedOn
END
CLOSE EXCURSOR
DEALLOCATE EXCURSOR
–COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
Any idea how to resolve this issue..?
thanx… cool tip.. it really works 4 me
thanks for your good articles.
i benefit from your good articles in your site
and i wish that you have good luck
Thanks alot for the article. keep it up.
I am working on 2 database servers, and both have ‘SQL_Latin_general*’ collate.
But in one server, following query not works,
“WHERE temp1.accountID IN
(SELECT temp1.accountID FROM table2)”
error : Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
It works for the following,
“WHERE #temp1.accountID COLLATE DATABASE_DEFAULT IN
(SELECT accountID COLLATE DATABASE_DEFAULT
FROM #temp2)”
Why this is happening,it complains that collate is ‘Latin_General*’ in the server… any ideas…
Hello!
Please, see the following query:
select p.[PdrString1] as NUM,
p.[PdrString2] as TONEID,
g.[6032_GlobalPromptsFile_Name] as TNAME,
convert(varchar,p.[PdrTime],20) as ADATE,
case when PdrString5 = ‘Script ID: 6118’ then PdrString4 COLLATE DATABASE_DEFAULT else s.des end as DES,
p.[PdrString5] as STATUS,
case when PdrString5 = ‘Script ID: 6118’ then ‘Manual’ else s.aname end as ATYPE,
p.[PdrString3] as CATID, r.[9181_CosName]
from dbo.[PDR-2009-05-23] p
left outer join [105-02-agg.bakcell.com].cdr.dbo.frbt_status s on s.code = p.[PdrString5] COLLATE DATABASE_DEFAULT and s.atype = p.[PdrString4] COLLATE DATABASE_DEFAULT,
[105-02-agg.bakcell.com].magnolia.dbo.GlobalPromptsFiles g, [105-10-rep].magnolia.dbo.SubsPPS v, [105-10-rep].magnolia.dbo.COS r
where p.pdrtype = 3
and p.[PdrString3] = g.[6050_GlobalPromptsCategory_Id]
and p.[PdrString2] = [6031_GlobalPromptsFile_Code] COLLATE DATABASE_DEFAULT
and v.[9061_SubsNumber]=p.[PdrString1]
and v.[9180_CosId]=r.[9180_CosId]
order by PdrTime desc
Oh, I found the solution. Thanks a lot!
Thanks Mr.Pinal,
i am feeling lucky,
and also feel more jealous on your knowledge ;)
whatever problem i face in sql server ,
you provide me a solution, Great….
Hello!
Could you help me to figure that out:
select count(distinct a.[9060_SubsId])
from ([CDR-OCT-2009].dbo.[CDR_Oct2009],
[CDR-NOV-2009].dbo.[CDR_Nov2009]) a
where ChargeableDuration>’0′
and OriginateTime BETWEEN ‘2009-11-01’ and ‘2009-11-18 23:59’
Wov…………It worked.Thanks a lot…
I have a problem while running the stored procedure.
I have 2 databases and tempdb
database 1 has Latin1_General_CI_AS collate
database 2 has Sql_Latin1_General_CP1_CI_AS collate
my tempdb has Latin1_General_CS_AS
I have created a stored procedure in database2.
Inside the stored procedure I have written the following code
create table #tble ([Stnum] nvarchar(5)COLLATE Latin1_General_CI_AS
)
insert into #tble
Select distinct [St_Number]
[database 1].dbo.stgtbl
update #tble
Set [[Stnum]]= CASE
WHEN LEN([Stnum]) = 1 THEN ‘0000’ + [Stnum]
WHEN LEN([Stnum]) = 2 THEN ‘000’ + [Stnum]
WHEN LEN([Stnum]) = 3 THEN ’00’ + [Stnum]
WHEN LEN([Stnum]) = 4 THEN ‘0’ + [Stnum]
END
WHERE LEN([Stnum]) < 5
while running the stored procedure i got an error
Invalid object name '#tble'
urgent reply needed
Thanks in advance
Anthuvan
Thanks a lot..
Thanks for your advice with the collation error, it saved us lot of time