SQL SERVER – Cannot resolve collation conflict for equal to operation

June 11, 2007 by pinaldave

Cannot resolve collation conflict for equal to operation.

In MS SQL SERVER, the collation can be set in column level. When compared 2 different collation column 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 following keywords around “=” operator.

SELECT ID FROM ItemsTable INNER JOIN AccountsTable WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT = AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Collation can affect following areas:

1) Where clauses

2) Join predicates

3) Functions

4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Collation | 180 Comments

180 Responses

on June 12, 2007 at 11:52 am | Reply Andrea

Thank you!! It really works!!
on July 2, 2007 at 4:12 am | Reply vaibhav

Thanks for the solution
on July 4, 2007 at 12:59 am | Reply Vlad

thanks for that! it really helped me
on July 6, 2007 at 3:22 am | Reply Santosh

Thanks a lots for simple and easy answer.
on July 12, 2007 at 9:27 am | Reply Rory

Agreed. Very helpful.
on July 18, 2007 at 12:06 am | Reply Mlden

Grerat!
on July 20, 2007 at 6:28 am | Reply Bairavi

Thanks a lot. Its very helpful to resolve the problem.
on July 21, 2007 at 4:20 am | Reply Tanuja

Thanks a lot. That was quick and simple answer.
on July 24, 2007 at 6:20 am | Reply Fabricio

Thank’s! The Best Solucion!
on July 29, 2007 at 4:49 am | Reply Gareth Stretch

Thanks for the help, I am still having a problem though maby i am being stupid. in my case i have a union select. see example

select top 10 ‘Listings’ as SearchItem , id as SearchResultID ,’Listings.aspx’ as PageToLoad ,Description as SearchDetail from StudentDirectoryItems
where Description like ‘%’ + @SearchValue + ‘%’ or Address like ‘%’ + @SearchValue + ‘%’ or PhoneNumbers like ‘%’ + @SearchValue + ‘%’ and VarsityID = @VarsityID
union

select top 10 ‘News’ as SearchItem , News.id as SearchResultID ,’HomePageNews.aspx?newsID’ as PageToLoad , News.ShortDescription as SearchDetail from News
left outer JOIN VarsityNews ON News.ID = VarsityNews.NewsID
where News.ShortDescription COLLATE DATABASE_DEFAULT like ‘%’ + @SearchValue COLLATE DATABASE_DEFAULT + ‘%’ or News.DisplayDescription COLLATE DATABASE_DEFAULT like ‘%’ + @SearchValue COLLATE DATABASE_DEFAULT + ‘%’ and VarsityNews.VarsityID = @VarsityID
union

Your help will be much appreciated.
on July 29, 2007 at 5:55 am | Reply pinaldave

Gareth,

change your code like :
where News.ShortDescription COLLATE DATABASE_DEFAULT like ‘%’ + @SearchValue + ‘%’ COLLATE DATABASE_DEFAULT or

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 10, 2007 at 2:55 am | Reply Madboy

Thanks! It worked
on August 10, 2007 at 8:28 am | Reply Andreas

Hi Pinal
i have the following query:

SELECT
Site.Site__ID,
Site.Site_Type,
Site.Site_Post_Code,
Site.Site_Town,
Site.Site_PhaseName,
Site.Site_Name,
Office.Office__ID,
Office.Office_Type,
Office.Office_Status,
Office.Office_Name,
[User].User_Organisation,
[User].User_PhaseName,
[User].User_FullName,
[User].User_EmailAddress
FROM Site WITH (NOLOCK)
LEFT JOIN
Site_SiteToOffice ON Site.Site__ID = Site_SiteToOffice.Site__ID
LEFT JOIN
Office ON Site_SiteToOffice.__Office__ID = Office.Office__ID
LEFT JOIN
Office_OfficeToUser ON Office.Office__ID = Office_OfficeToUser.Office__ID
LEFT JOIN
[User] ON Office_OfficeToUser.__User__ID = [User].User__ID
LEFT JOIN
Site_SiteToUser ON Site.Site__ID = Site_SiteToUser.Site__ID AND [User].User__ID = Site_SiteToUser.__User__ID
where
(len (@CompanyName_key)=0 OR Office_Name COLLATE DATABASE_DEFAULT like @CompanyName_key )
AND (len (@UserName_key)=0 OR User_FullName COLLATE DATABASE_DEFAULT like @UserName_key )
AND (len (@UserEmail_key)=0 OR User_EmailAddress COLLATE DATABASE_DEFAULT like @UserEmail_key )
AND (len (@SiteName_key)=0 OR Site.Site_Name COLLATE DATABASE_DEFAULT like @SiteName_key )

Although i have placed the collate database_deafault i still get the error? any suggestions?
on August 10, 2007 at 8:35 am | Reply Andreas

neddless to say that the parameters where decalred earlier on etc.
on August 10, 2007 at 9:15 am | Reply LEWIS

thanks!
on August 13, 2007 at 5:45 pm | Reply Mente

Chingón!
on August 14, 2007 at 11:12 am | Reply Dru

Very Helpful….thanks.
on August 16, 2007 at 12:16 pm | Reply SHAMEL

This work , very helpful
Thanks
on August 17, 2007 at 8:59 am | Reply Rafael

Hi!!!

How can i use the COLLATE DATABASE_DEFAULT in this update

update a set area = b.area from tbl_lnventas as a
inner join [10.105.162.223].dbunica3.dbo.cat_entidad as b on b.codigolada = left(a.ctelefono,8)
where a.area=”
go
on August 27, 2007 at 2:01 pm | Reply Jeffrey

Great help! I ran into an issue with a case statemen and found an answer that might be helpful to someone else. Looked up “Collation Precendence” in books online and found a quick solution to a problem I don’t have time to fix the root cause for:

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE DATABASE_DEFAULT
FROM TestTab
on August 30, 2007 at 7:41 am | Reply SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS Journey to SQL Authority with Pinal Dave

[...] 30th, 2007 by pinaldave This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to [...]
on September 3, 2007 at 9:53 am | Reply GAP

Thanks a lot!
on September 12, 2007 at 9:12 am | Reply filip

Thanks for your advice with the collation error, it saved us lot of time.
on September 12, 2007 at 11:20 pm | Reply Lieven

Eersteklas!
Works like a charm!
on September 13, 2007 at 6:59 am | Reply Percy

Gracias, me saco del problema
on September 14, 2007 at 10:41 am | Reply veronica

Thank!!!
on September 14, 2007 at 1:01 pm | Reply Andy

let me be # 27 to say thanx…
on September 20, 2007 at 5:29 am | Reply Will

Thanks dude -you’ve made life easier!
on September 20, 2007 at 9:21 am | Reply langdotnet

Thanks!!!
on September 24, 2007 at 10:02 pm | Reply Qumrul Hasan

Thanks a lot. !!!
on September 26, 2007 at 3:59 am | Reply J. Bornemann

Great help!
Googled “Cannot resolve collation conflict for equal to operation” and the first hit was this perfect solution!

Many Thx!
on October 16, 2007 at 6:28 pm | Reply kaven

Hi,

I got the errros below, did anyone know about it?
Expression type int is invalid for COLLATE clause

Thanks
on October 22, 2007 at 10:41 am | Reply Oxana

Thank you so much! Nice and easy solution!
on October 24, 2007 at 2:40 am | Reply Farzad

Thanks!!! It Realy Works !!!
on October 30, 2007 at 6:38 am | Reply rpd

Thanks! This worked well for a database move I’m working on. I needed to concatenate a bunch of obsolete fields to dump into another new field. Here’s my example:

select
‘varchar_field1: ‘+varchar_field1+’
varchar_field2: ‘+varchar_field2+’
varchar_field3: ‘+varchar_field1+’
text_field1: ‘+cast(text_field1as varchar(800)) COLLATE DATABASE_DEFAULT
from my_table

Without collate database_default, I’d get this error:
Cannot resolve collation conflict for column 1 in SELECT statement.
on November 3, 2007 at 3:21 am | Reply Jason

Great… Going live with a major project and this little gem was a sweet, quick and perfect resolution

Cheers
on November 5, 2007 at 11:56 pm | Reply Dhong

Superb!!! Thank you!
on November 14, 2007 at 12:38 pm | Reply amer ms

hi

i too face this problem and found that the collation of one of the database is different. now i want to make the collation same. can i change the collation of an existing database and will it update collation for all existing tables and columns of that database.
on November 15, 2007 at 12:55 am | Reply Bola

Thank you,

Really appreciate it.
on November 19, 2007 at 11:38 am | Reply hiren shah

This work , very helpful to you
It Realy Works !!!
Thanks
on November 20, 2007 at 2:10 am | Reply Mark

Wow this works but just wondering why it works?

Thanks
on November 20, 2007 at 9:48 pm | Reply me

Thanks a lot
on November 22, 2007 at 2:33 pm | Reply Lovely

HI Mr. Pinal Dave, such a great help, the collation problem wasnt there o the query i sent earlier, bacause that was incomplete…

the complete query plus the solution:::

set @str = ’select t0.docnum as SORefNo, t0.DocDueDate as SODocDueDate, t0.cardCode as CustomerCode, t7.cardName as CustomerName,
t1.itemcode as ItemCode, t4.ItemName as ItemName, t5.WhsName as WareHouseName, t6.U_NAME as UserName,
t1.quantity as SOQty, t2.quantity as SIQty,
case
when t1.quantity<=t2.quantity then 0 else t3.quantity
end as DraftSIQty, t1.quantity – t2.quantity as ”BackOrder”
from ordr t0 inner join
rdr1 t1 on t0.docentry=t1.docentry inner join
ocrd t7 on t0.CardCode=t7.CardCode inner join
ousr t6 on t0.Usersign=t6.UserId inner join
oitm t4 on t1.itemcode=t4.itemcode left join
owhs t5 on t1.Whscode=t5.Whscode left join

(select t10.draftkey, t11.baseentry, t11.baseline, t11.quantity from
oinv t10 inner join inv1 t11 on t10.docentry=t11.docentry) t2 on
t2.baseentry=t1.docentry and t2.baseline=t1.linenum left join
drf1 t3 on t3.baseentry=t1.docentry and t3.baseline=t1.linenum and (t2.draftkeyt3.docentry or t2.quantity is null)
where (t0.docduedate between ”’+convert(varchar(20),@startDate,102)+”’ and ”’+convert(varchar(20),@EndDate,102)+”’ ) ‘

– @whsCode
if ( @whsCode ‘NULL’ )
Begin
SET @str = @str + ‘ and ( (t1.Whscode COLLATE DATABASE_DEFAULT in (Select Value from fnSplitter(”’+ @whsCode +”’) ) ) or (t1.Whscode COLLATE DATABASE_DEFAULT is null) )’
End
Else
Begin
SET @str = @str + ‘ and((t1.Whscode in (t1.Whscode)) or (t1.Whscode is null))’
End
SET @str = @str + ‘ and not(t2.quantity is null and t3.quantity is null) ‘
SET @str = @str + ‘ order by docnum ‘

exec (@str)

Mr.Pinal, ur such a genius, i want to be ur friend.. u may not be aware but u helped me already with a lot of things, ur articles was so useful…..thanks thanks for the solution… :D:D:D
on November 27, 2007 at 8:05 pm | Reply Jean

Thanks a million for the blog post Pinal.

Very usefull stuff.

:)

Jean
on November 28, 2007 at 4:38 pm | Reply Madhumita

Thanks a lottttttt!!!!! It worked!
on November 29, 2007 at 4:31 pm | Reply Aftab

HI Mr. Pinal Dave,

How can we change the collation of a column???

what r the different types of collation in MS Sql??

Please help me asp.
Thanks in advance…
on November 29, 2007 at 4:33 pm | Reply Aftab

HI Mr. Pinal Dave,

I want to find the list of those employees whose name starts with either A or B without using union query.
on November 30, 2007 at 3:42 pm | Reply Jason

Thanks Pinal,

Saved me a lot of searching.

Jason ;-)
on December 5, 2007 at 12:43 pm | Reply Marlene

Could you please help, i get the collation error with this query:
SELECT *
FROM ctbl_ReplenishOrders RO
left JOIN #tbl_OutputOrdered OO ON RO.OrderDate = OO.OrderedDate AND RO.Itemcode COLLATE DATABASE_DEFAULT = COLLATE DATABASE_DEFAULT OO.ItemCode
WHERE OO.Itemcode IS NULL

Even though i am trying your collation solution, it seems still not to work. Can you please help?
Many thanks!marlene
on December 9, 2007 at 6:09 pm | Reply Ehsan

wow, u r wonderful dude
on December 12, 2007 at 5:09 pm | Reply John

PD – thanks for the post!

Marlene , try:

SELECT *
FROM ctbl_ReplenishOrders RO
left JOIN #tbl_OutputOrdered OO ON RO.OrderDate = OO.OrderedDate AND RO.Itemcode COLLATE DATABASE_DEFAULT = OO.ItemCode COLLATE DATABASE_DEFAULT
WHERE OO.Itemcode IS NULL
on December 12, 2007 at 9:03 pm | Reply srn

Thanks Pinal,
It works.
on December 12, 2007 at 10:16 pm | Reply Subu

Excellent this worked perfectly. Thanks!!!
on December 13, 2007 at 12:44 pm | Reply RW

Very useful tip. Thanks, perfect answer!
on December 13, 2007 at 6:42 pm | Reply Miikka Salonen

Thanks. I had problem with temporary table in join and this was nice and easy fix!
on December 19, 2007 at 1:33 am | Reply Karen

Thanks so much for the tip! It was very helpful.
on December 21, 2007 at 11:15 pm | Reply Carlos (phi)

Gracias, salvaste mi dia XD.
Thank you saved my day XD.
on January 16, 2008 at 6:14 pm | Reply BT

Thankyou , really helped me.
on January 18, 2008 at 5:05 am | Reply David Naas

It’s works! you kick ass dude
on January 29, 2008 at 8:20 pm | Reply John Motson

Thanks Pinal, that saved the day for me!

John
on January 30, 2008 at 12:05 am | Reply sudharsan

Hi dave….awesome….a solution made to look so simple for quite a complex problem (it was definetly complex for me…:)…thanks a ton
on January 30, 2008 at 2:27 am | Reply Sheau Jeng

Very helpful! Thank you…
on February 4, 2008 at 8:43 pm | Reply Turker Tunali

Great help. It really works. Great job.
that saved the day for me
on February 14, 2008 at 4:42 pm | Reply Greg

Excellent – simple post and resolved my problem – much appreciated!!
on February 15, 2008 at 12:14 am | Reply John Yoder

Worked for me. Thanks for the post!
on February 15, 2008 at 12:31 pm | Reply liquidskinn

hey, thanks so much for posting this. it really helped me. so much. :D

thanks thanks thanks! :)
on February 15, 2008 at 5:36 pm | Reply Graham

brill thanks, got me out of sticky jam
on February 29, 2008 at 8:10 pm | Reply jack

Great! Huge thank you! Regret that I haven’t found this earlier.
on March 18, 2008 at 12:37 pm | Reply Mindaugas

Wow man, it works. Very appreciate….
on March 19, 2008 at 11:20 am | Reply Ehsan

Great, it is a great help
on March 20, 2008 at 5:57 am | Reply Bin

Very good mate, thanks
on March 28, 2008 at 9:44 pm | Reply Visgor

I’m trying the following syntax:

SELECT count(*) FROM [f_matter] M (nolock) LEFT JOIN #Matter T ON M.matter_Num COLLATE SQL_Latin1_General_Pref_CP850_CI_AS = COLLATE SQL_Latin1_General_Pref_CP850_CI_AS T.Matter

Getting error “Incorrect syntax near ‘COLLATE’.”
Please help. What am I doing wrong?
Thanks
on April 1, 2008 at 12:55 pm | Reply Eugene

Thanks a million,. you are a lifesaver.
on April 10, 2008 at 4:23 pm | Reply Amit Garg

Simply Great help…….
on April 14, 2008 at 3:56 pm | Reply Vergil

Thanks a lot :-)
on April 14, 2008 at 5:47 pm | Reply Melody

Thank you too much! It’s helpful!
on May 5, 2008 at 2:30 am | Reply salman

Thanks a lot, it happend to me in a case statement.
on May 13, 2008 at 2:07 pm | Reply Manjula

Hello, Marvellous Dave….it really worked for me. Thank you very much.

Cheers :)
on May 13, 2008 at 6:17 pm | Reply Shearer

Great, strange issue i felt. Thanks for the solution.
on May 26, 2008 at 10:45 am | Reply aibvhiey

thanks a lot..
on May 26, 2008 at 11:04 am | Reply aibvhiey

thank you very very much..
on May 27, 2008 at 2:47 pm | Reply Nives

Thank you very much! It is a great help!
on June 4, 2008 at 8:33 am | Reply krisna

thank you sir, you save my day ^^
on June 4, 2008 at 9:37 am | Reply Thiam Hooi

Great solution from the original post… Thanks a lot
on June 12, 2008 at 3:39 pm | Reply shahzad

Thank you very much! It is a great help!
on June 16, 2008 at 7:53 pm | Reply Darren Thmpson

Cheers mate! :o)
on June 16, 2008 at 7:59 pm | Reply Aash

Hi have the below query and cannot quite figure out where excatly to use the collation.

Can somebody please help:

INSERT INTO CRMSUBSCRIPTIONSV1_HITS(USER_LOGONNAME, USER_PASSWORD, ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED)
SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_ISHITBASED = ‘1′ AND NOT EXISTS
(SELECT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
FROM CRMSUBSCRIPTIONSV1_HITS,CRMSUBSCRIPTIONSV1 WHERE
CRMSUBSCRIPTIONSV1.USER_LOGONNAME = CRMSUBSCRIPTIONSV1_HITS.USER_LOGONNAME AND
CRMSUBSCRIPTIONSV1.USER_PASSWORD = CRMSUBSCRIPTIONSV1_HITS.USER_PASSWORD AND
CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID = CRMSUBSCRIPTIONSV1_HITS.ORDER_ORDERPRODUCTID AND
CRMSUBSCRIPTIONSV1.ORDER_HITSPURCHASED = CRMSUBSCRIPTIONSV1_HITS.ORDER_HITSPURCHASED)
GROUP BY ORDER_ORDERPRODUCTID,USER_LOGONNAME,USER_PASSWORD,ORDER_HITSPURCHASED

Thanks in advance.
on June 21, 2008 at 1:08 am | Reply Lala Ramnath

Works!!!!!!!!!!!1
on July 10, 2008 at 9:32 pm | Reply Bongski

Thanks you very much sir. You’re the man!
on July 16, 2008 at 12:42 pm | Reply Benni

Great! Worked!
Thank you very much…
on July 22, 2008 at 5:47 pm | Reply Kruno

Thank you.
on August 5, 2008 at 3:54 am | Reply mc

Thanks Pinal!
on August 14, 2008 at 8:34 am | Reply Dinesh

I would like to add that use Collate with ‘IN’ clause also.

Have a look in my Query which I solved using collate for ‘IN’ clause.

SELECT DISTINCT top 100 PERCENT FT_TBL.product, FT_TBL.RSV_desc, FT_TBL.LIST_PRICE, FT_TBL.uom, KEY_TBL.RANK, PDoc.product AS PDocProduct,
FT_TBL.pgroup, FT_TBL.Special_Conditions, FT_TBL.shop_id, SUM([Order Contents].Qty) AS Sqty
FROM Products1 AS FT_TBL INNER JOIN
FREETEXTTABLE(Products1, *, ‘fuel pump’) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] LEFT OUTER JOIN
[Order Contents] ON FT_TBL.product COLLATE DATABASE_DEFAULT = [Order Contents].[Item Number] COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN
(SELECT product
FROM ProductDocs
WHERE ([Shop ID] IS NULL)) AS PDoc ON FT_TBL.product COLLATE DATABASE_DEFAULT = PDoc.product COLLATE DATABASE_DEFAULT
WHERE (FT_TBL.shop_id IS NULL) AND (FT_TBL.car IN
(select DISTINCT [Car Type] FROM [Car Types]))
GROUP BY FT_TBL.product, FT_TBL.RSV_desc, FT_TBL.LIST_PRICE, FT_TBL.uom, KEY_TBL.RANK, PDoc.product, FT_TBL.pgroup, FT_TBL.Special_Conditions,
FT_TBL.shop_id
ORDER BY KEY_TBL.RANK DESC
on August 19, 2008 at 12:23 pm | Reply JohnTags

thanks you very much for nice tips! Its save my day. :)
on August 30, 2008 at 12:57 am | Reply zoran035

thanks you very much for nice tips!
(Ovo je bila nocna mora dosad – Hvala (Serbian) )
on September 4, 2008 at 4:55 am | Reply Fabian

Thanks you very much, Greate.
on September 4, 2008 at 3:31 pm | Reply Subhrangshu Banerjee

Thanks Pinal.
It has helped me a lot.
on September 4, 2008 at 9:51 pm | Reply Fabio

YES !!!! THANK YOU VERY VERY MUCH

MERCI BEAUCOUP (French)

MECI EN PIL (FWI Creol)

:-D
on September 11, 2008 at 5:00 pm | Reply Harthikote Naveen

Superb boss!!!!!!! thank u for ur knowledge sharing.
on September 13, 2008 at 12:31 pm | Reply Florian

Thank you very much!
on September 20, 2008 at 4:27 pm | Reply Bhumit

Its really works

Thank you very much
on September 25, 2008 at 2:13 pm | Reply Ashish

Thnaks for this Article. Really it helped me.
on October 1, 2008 at 5:55 pm | Reply Sara

Hi i need a help with collation problem

I am creating a database with sql_latin_general collation in an sql instance with collation latin_general.

While running installed application error occurs.is there any way to handle this scenario.

Please help me its very urgent
on October 7, 2008 at 12:48 am | Reply Luciana

Thanks, very helpful!!!
on October 28, 2008 at 12:04 am | Reply Amoril

Exactly what I needed, thanks.
on October 31, 2008 at 2:33 pm | Reply Theo

Very tidy, saved me from my ALTER TABLE nightmare against an 30Gb db
on November 4, 2008 at 5:01 pm | Reply WOW

Thanks!
on November 6, 2008 at 2:29 am | Reply Marvin

Thanks !!!

This help me with this …

WHERE ESTADO_PROCESO COLLATE DATABASE_DEFAULT in (select string from dbo.fn_SplitStrings(@as_estados, ‘,’))
on November 11, 2008 at 2:25 pm | Reply Me

Thank you very much! Nice tip!
on December 4, 2008 at 3:28 pm | Reply GuS

Worked like a charm, thanks man :)
on December 9, 2008 at 1:35 pm | Reply MS

Many Many Thanks :)

It worked out for me!!!!!!!!!!!!

Plz kip up the gud work.
on December 12, 2008 at 12:50 pm | Reply Vinod

Thanks, it works!
on December 17, 2008 at 12:04 pm | Reply Sohbet

thank you baby
on December 22, 2008 at 3:45 pm | Reply Ruan

Hi guys

I have added the COLLATE DATABASE DEFAULT after my join clauses, now i get an error: Expression type int is invalid for COLLATE clause.

Here is the query, can someone please help??

SELECT
I.Name,
A.Name EventShortName,
ISNULL(F.Name, ”) GroupShortDisplay,
C.ParameterName,
D.WinningValue,
COUNT(EntrantID) TotalEntries,
CASE WHEN D.WinningValue = G.Pick THEN
COUNT(EntrantID)
ELSE
0
END CorretEntries,
CASE WHEN D.WinningValue G.Pick THEN
COUNT(EntrantID)
ELSE
0
END IncorrectEntries

FROM [Pikum.Web].dbo.Event A WITH (NOLOCK)

INNER JOIN [Pikum.Web].dbo.[EventParameter] B WITH (NOLOCK)
ON B.EventID = A.EventID COLLATE DATABASE_DEFAULT

INNER JOIN [Pikum.Web].[dbo].[Parameter] C WITH (NOLOCK)
ON C.ParameterID = B.ParameterID COLLATE DATABASE_DEFAULT

INNER JOIN [Pikum.Web].dbo.[PikumEventParameter] D WITH (NOLOCK)
ON D.EventParameterID = B.EventParameterID COLLATE DATABASE_DEFAULT

LEFT JOIN [Pikum.Web].dbo.EventCategoryGroup E (NOLOCK)
ON E.EventID = A.EventID COLLATE DATABASE_DEFAULT
AND E.EventCategoryGroupID = D.EventCategoryGroupID COLLATE DATABASE_DEFAULT

LEFT JOIN [Pikum.Web].dbo.CategoryGroup F (NOLOCK)
ON F.CategoryGroupID = E.CategoryGroupID COLLATE DATABASE_DEFAULT

INNER JOIN [Pikum.Web].dbo.[EntrantPick] G WITH (NOLOCK)
ON G.PikumEventParameterID = D.PikumEventParameterID COLLATE DATABASE_DEFAULT

INNER JOIN [Pikum.Web].dbo.Pikum I WITH (NOLOCK)
ON I.PikumID = D.PikumID COLLATE DATABASE_DEFAULT

WHERE PikumStatusID >= 7
AND UserPikumCreatorID = 1566
and pikumidentifier = ‘V7Y2A-D4F40′

GROUP BY

I.Name,
A.Name,
ISNULL(F.Name, ”),
C.ParameterName,
D.WinningValue,
G.Pick
on December 22, 2008 at 11:57 pm | Reply TimothyAWiseman

Thank you!
on December 30, 2008 at 10:24 am | Reply RobinAEllis

worked like a charm, thanks….
on January 4, 2009 at 6:09 pm | Reply Reza

Thanks, very helpful!!!
on January 8, 2009 at 9:23 pm | Reply Mukul

Correct and Simple solution.
Good Work.
on January 20, 2009 at 1:16 pm | Reply Sohpet

thanks
on January 28, 2009 at 1:14 am | Reply Agustin

Can one of theese errors hang and a restart the SQLServerAgent service?
on January 30, 2009 at 8:06 pm | Reply PorkPie

Thanks – Great Solution
on February 2, 2009 at 8:44 pm | Reply Sasi

Thanks… It solved my problem
on February 9, 2009 at 6:42 pm | Reply OBarahmeh

Let me thank you in 2009.02.09
Great Solution.
on February 25, 2009 at 12:02 pm | Reply SQL SERVER - Change Collation of Database Column - T-SQL Script Journey to SQL Authority with Pinal Dave

[...] if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit [...]
on March 5, 2009 at 1:05 pm | Reply Kasun Kularathne

Hi…. thank u so much …it’s really work for me :-)
on March 13, 2009 at 12:52 pm | Reply sims

Genius!
on March 19, 2009 at 12:27 pm | Reply Roopsundar

Thanks a lot!!!!
on March 23, 2009 at 11:16 am | Reply mickeymoo

Helped me too when I was doing an update query.

I didn’t even know what collation was before this!
on March 24, 2009 at 9:41 pm | Reply Avineet

I get this error, when I add the clause, Expression type numeric is invalid for COLLATE clause.

I have added as :

ON tablename.Columnname COLLATE DATABASE_DEFAULT = tablename.columnname COLLATE DATABASE_DEFAULT
on March 30, 2009 at 2:02 pm | Reply krishna murthi b

Worked for me. Simple solution.

thanks,
Murthi
on April 7, 2009 at 1:54 am | Reply Sriram Prasad

Thanks,Simple Solution.

But how can i make the two tables to have same collation?
I don’t want to have different collation for tables.

How can i know a table/column’s collation?
on April 8, 2009 at 12:08 pm | Reply Nazanin

God bless you. your solution is really good.

Thanks a lot.
on April 9, 2009 at 11:58 pm | Reply Alex the DBA

thanks!

worked like a charm!
on April 24, 2009 at 4:37 pm | Reply Bhavesh

Its simply great. Thanks.
on May 5, 2009 at 4:55 pm | Reply Tassos

Thank you. Your solution saved me from hour of work.
on May 9, 2009 at 1:49 am | Reply Justin

Thanks! You saved my life!
on May 15, 2009 at 6:37 pm | Reply cannibalcat

Awesome… quick easy answer, saved us some time this morning! Thanks
on May 18, 2009 at 8:34 pm | Reply Sandeep

Hey, we have one ready applicaion…it’s working fine in most of the customer but only one customer getting this error.

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot resolve collation conflict for equal to operation.

How to resolve this error with out chnage in code?
how to resolve this issue using SQL SERVER enterprise manager?

Regards / Sandeep
on May 25, 2009 at 2:11 pm | Reply padmore

You saved my day. Thank!
on June 17, 2009 at 7:46 pm | Reply Roberto

Thanks a lot.
I had this problem and with this solution my query working fine.
A litlle diference between collates from diferent databases make this error.
Theres a one thing that I have doubt.
In my case I have 2 dbo’s
The firts have this COLLATE
SQL_Latin1_General_CP1_CI_AS
The other have this
Latin1_General_CI_AS

Whats the diference?
And theres a way to choose the right COLLATE in my query?

Sorry my english, I am brazilian…
Thanks again
on June 18, 2009 at 10:40 am | Reply priya

I have collation error -’Cannot resolve collation conflict for equal to operation’ when calling a function that returns a table. The function is as below:
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Data NVARCHAR(10) NULL
)
AS
BEGIN

Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Then i added the server’s collation name as :

RETURNS @RtnValue table
(

Id int identity(1,1),
Data NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Then it is working fine. But i would like to know how to make this dynamic so that everytime the server’s collation property changes, i need not change the function.
on June 18, 2009 at 10:50 am | Reply priya

for the above problem, i tried using database_default but it threw the same error. Is there anything like server_default?

Thank you for the help.
on June 24, 2009 at 12:31 am | Reply YumYum

Yeah
on June 24, 2009 at 8:03 pm | Reply Josh Curtiss

The error message is useless to help us know this is what we should do. But your tip is perfect. Thanks for the help.
on June 24, 2009 at 10:58 pm | Reply Luis

Thanks a lot!
on July 4, 2009 at 7:14 pm | Reply medyum

Hi!!!

How can i use the COLLATE DATABASE_DEFAULT in this update

update a set area = b.area from tbl_lnventas as a
inner join [10.105.162.223].dbunica3.dbo.cat_entidad as b on b.codigolada = left(a.ctelefono,8)
where a.area=”
go
on July 9, 2009 at 7:31 pm | Reply Bill

Great solution. Solved my problem.
on July 15, 2009 at 1:10 pm | Reply Nicolas

Thanks a lot!!!
Worked for me :)
on July 23, 2009 at 8:11 pm | Reply ssk

Hi i need a help with collation problem

I am creating a database with sql_latin_general collation in an sql instance with collation latin_general.

While running installed application error occurs.is there any way to handle this scenario.

Please help me its very urgent
on July 28, 2009 at 3:52 pm | Reply TSP

Thanks a lot.
It has saved lot of our time.
Whew Superb!!!
on July 30, 2009 at 7:03 pm | Reply Gerry

Thanks Pinalkumar, worked a treat for me.

Thank God for forums and the people like you who give their expertise so freely and unselfishly.
on July 31, 2009 at 1:48 pm | Reply Freek Van Mensel

Thanks a lot.
It was very helpful for me.
on August 10, 2009 at 6:07 pm | Reply medyum

This work , very helpful to you
It Realy Works !!!
Thanks
on August 18, 2009 at 12:29 pm | Reply Maurits

Thanks from me too!
on August 20, 2009 at 9:22 pm | Reply Gonza

Gracias, muchas gracias.
on August 21, 2009 at 5:34 am | Reply sohbet

thanks admin
on August 21, 2009 at 8:26 pm | Reply Bill Dawson

Gosh, thanks. I hate this problem and I always forget the solution, so finally I’ve bookmarked this page.

You’re a life saver. Nice to see a 2 year-old blog post bring satisfaction to so many!
- on August 21, 2009 at 8:28 pm pinaldave
  
  Bill Dawson,
  
  Thank you for your kind words!
  
  Regards,
  Pinal
on September 4, 2009 at 12:48 pm | Reply TonyS.

I just saw this and, from an educational perspective, want to say that the original statement at the top of this post has a syntax error in that it is missing the ON clause and therefore won’t compile.

Having said that, the information contained therein was useful.

Tony S.
on September 5, 2009 at 2:46 am | Reply Cisnetyc

Excellent solution!!! this helped me to fix my problem
on September 16, 2009 at 11:22 am | Reply Ashish

Thanks, It solved my problem
on September 17, 2009 at 4:43 pm | Reply Simar

hi i was wondering if you can help. I am testing a script in SQL server 2000 (Query Analyzer) and everytime i am doin a runtime it gives me the following error:
Cannot resolve collation conflict for equal to operation.

my code is this

select Count(distinct username) as Failed from

(select Quiz.quizId,QuizAttachment.nodeID
from Quiz,QuizAttachment,PolicySet
where Quiz.quizID=QuizAttachment.quizID
and QuizAttachment.nodeID=PolicySet.nodeID
and Quiz.active=1)PartialQuizDetails

any idea on how to resolve this? would most appreciate it
on September 17, 2009 at 4:46 pm | Reply Simar

oh and have to mention that my collation on my database that im using is on SQL_LATIN_GENERAL_CP1_CI_AS
on September 17, 2009 at 9:34 pm | Reply Brian Tkatch

@Simar

One issue is that the COUNT works on username, which is not a field in the SELECT clause of the sub-query.
on September 18, 2009 at 10:20 pm | Reply Tony

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
on September 22, 2009 at 11:25 pm | Reply videolar

Thanks a lot. Good idea..
on September 28, 2009 at 9:12 pm | Reply John

Great post brother, keep up the good work.
on September 29, 2009 at 8:22 pm | Reply Lucho

good!
on October 1, 2009 at 12:36 am | Reply Cassiano

Thanks a lot!!!
on October 5, 2009 at 8:45 pm | Reply Anu

How can I use the Collate clause if I am matching say –

Select accountID from Table1
where AccountID in (select accountID from table2)
on October 13, 2009 at 7:19 pm | Reply Giorgos

Very helpful, thank you!
on October 13, 2009 at 7:21 pm | Reply Giorgos

Anu this worked fine for me:

“WHERE accountID COLLATE DATABASE_DEFAULT IN
(SELECT accountID COLLATE DATABASE_DEFAULT
FROM table2)”
on October 19, 2009 at 8:11 pm | Reply nash

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..?
on October 22, 2009 at 9:32 am | Reply Erick

thanx… cool tip.. it really works 4 me
on October 25, 2009 at 2:22 pm | Reply Hamiddo

thanks for your good articles.
i benefit from your good articles in your site
and i wish that you have good luck
on November 5, 2009 at 6:21 pm | Reply Dhanuka

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…
on November 6, 2009 at 12:55 pm | Reply Nigar

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
on November 6, 2009 at 12:57 pm | Reply Nigar

Oh, I found the solution. Thanks a lot!
on November 9, 2009 at 6:50 pm | Reply Annamalai

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….
on November 20, 2009 at 11:33 am | Reply Nigar

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′