SQL SERVER – Cannot resolve collation conflict for equal to operation

« SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

SQL SERVER – LEN and DATALENGTH of NULL Simple Example »

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 at the 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

Watch this quick video for resolution:

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)

Checkout following video how collation can change the order of the column:

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

About these ads

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

329 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 March 25, 2012 at 8:25 pm | Reply Sandeep Arora
  
  Put the ‘Collate Database_Default’ in front of the column names. Like Column _name Collate Database_Default. The error will be resolved.
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 | Reply 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′
on November 26, 2009 at 6:11 pm | Reply Srikrishna

Wov…………It worked.Thanks a lot…
on December 11, 2009 at 1:37 pm | Reply Anthuvan

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
on December 22, 2009 at 5:53 am | Reply notebook parçaları

Thanks a lot..
on December 24, 2009 at 2:44 pm | Reply ankara parke

Thanks for your advice with the collation error, it saved us lot of time
on December 31, 2009 at 1:40 am | Reply thanks

thanks dude!
on January 14, 2010 at 11:26 pm | Reply Michellea David

You are awsome. Thank you for your tips. This saved me on an error issue.
Another query I found useful to find the differences in my two collation types is:

select * from fn_helpcollations()
where name = ‘SQL_Latin1_General_CP1_CI_AS’
or name = ‘Latin1_General_CI_AS’
on January 22, 2010 at 7:49 pm | Reply kapadokya

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 January 30, 2010 at 7:02 am | Reply SQL SERVER – Several Readers Questions and Readers Answers Journey to SQL Authority with Pinal Dave

[...] To resolve this issue, you can either specify collation with column name in t-sql statement or change the collation of column permanently by altering the table, database or even server. For more details on how to specify collation in t-sql statement, please check following article: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-op… [...]
on February 11, 2010 at 9:04 am | Reply Dave

Great help thanks.

I am doing a join on a block of data created with a select and a view. As far as I can see there is no way to set the collation on the columns so you would expect them to the database default anyway.

Your solution gets it going.

Thanks again
on February 16, 2010 at 3:01 am | Reply csmeutah

I’ve been running queries from a local db server against a remote db server joining tables between the two. We moved our databases to new hardware and now the remote server gives us the collation error when we run the queries where it never did before. All the databases were restored so the db environment should be exactly the same, any idea why it would start generating these errors with just a hardware change?

I tried changing the “collation compatible” setting on the remote server from false to true but then the connection failed. Isn’t there some server setting that would fix this for me?
on March 11, 2010 at 1:01 am | Reply Ed Dantes

Thanks a bunch!
on March 11, 2010 at 7:19 pm | Reply Yoel

Thank you. This is exactly the type of thing you don’t think about until it hits you unexpectedly.
on March 24, 2010 at 6:35 am | Reply Sixto

Muchas Gracias por la informacion.
on March 24, 2010 at 10:45 pm | Reply Khalid

Very Helpful
thanks.
on March 25, 2010 at 8:18 am | Reply Klaus

Hi
I’ve been running queries from a local windows mobile device (SqlClient on device) against a remote db server (SQL 2005) We have a Table which has two Collations one is Default one is Chinese_PRC_BIN (column name is called name). When I using following SQL select string SELECT supplier_code,name FROM code_relations WHERE name COLLATE DATABASE_DEFAULT ”, It return NullreferenceException. If I only select supplier_code it works fine.

Please give me help!
on March 25, 2010 at 11:42 pm | Reply Mukesh

Thanks Pinal.

This helped me a lot and saved a lot of my time.
on April 12, 2010 at 7:08 am | Reply dna

Great, Thanks so Much.
on April 14, 2010 at 2:08 am | Reply Patrick McCarthy

Thanks for this, much appreciated
on April 14, 2010 at 7:53 pm | Reply James Collett

Great stuff! Thanks. A one-line fix that saved hours of woe and searching.
on April 28, 2010 at 1:24 pm | Reply Andrea

@Klaus: is there a copy & paste issue or the where clause is incomplete?

SELECT supplier_code,name
FROM code_relations
WHERE name COLLATE DATABASE_DEFAULT
on May 11, 2010 at 7:49 pm | Reply Paul

Cheers mate!
on May 17, 2010 at 1:32 pm | Reply Thank You

Thank you.
My problem is solved..
on May 17, 2010 at 3:09 pm | Reply Peter

Hi – don’t understand where this error suddenly appeared from but your fix work liked a charm thankyou
on May 17, 2010 at 5:27 pm | Reply brijesh

it is working perfectly. thanks for the very useful post.
on May 20, 2010 at 5:24 pm | Reply iman

thank you. I had a problem with case and union that is solved like this.
the error was on a nvarchar column

CASE WHEN c1= 1 THEN c2_nvarchar COLLATE DATABASE_DEFAULT
ELSE c3_nvarchar COLLATE DATABASE_DEFAULT
on May 20, 2010 at 6:21 pm | Reply Bruce Talcott

Thanks a lot!!!!!
on June 4, 2010 at 10:29 pm | Reply Frank Pearson

Thanks. Worked well.
on June 8, 2010 at 7:18 pm | Reply Salvador Solís

Someone know a way for change the collation in all tables and collumns, I migrated several Oracle Databases to SQLServer 2005 and a .net application in many servers with the same configutation but specially now in one server gave me collation errros. In my .net project I’m having several problems with collation in most querys. Thanks in advance.
on June 16, 2010 at 7:36 pm | Reply Alan Winn

Thank you — it worked GREAT!

Mr. Dave for President!!
on June 24, 2010 at 2:34 pm | Reply David

Works fine!! THANKSSSSSSSS!!
on June 28, 2010 at 5:22 pm | Reply Irshad Ali

Thank you Mr.Pinal
on July 7, 2010 at 10:59 am | Reply Humayun

Great….

Thanks………it solved my problem in less than a minute

i appreciate
on July 8, 2010 at 4:46 pm | Reply sidiq

Thanks a Lot , it really solved My problem at Last Stage of Deployment

Regards

Sidiq

[Email removed]
on July 19, 2010 at 2:43 pm | Reply An

Thank you so much, I have found this solution for a long time!
on July 20, 2010 at 5:00 pm | Reply Eghbaldar

THX a lotttttttttttttttttttt :-x
on July 20, 2010 at 6:19 pm | Reply Daniel Ionescu

Thank you for the solution.
on July 22, 2010 at 10:25 pm | Reply kapadokya

very good blog thanks for web..
on July 23, 2010 at 3:49 am | Reply kapadokya

very nice posted thanks
on July 26, 2010 at 12:42 pm | Reply sabarish

Great small work around…
on July 27, 2010 at 4:31 am | Reply Rossanne

Thanks for all your helpful posts.
on August 5, 2010 at 6:53 pm | Reply Isaac

Thanks a lot.

This is very helpfull~~
on August 10, 2010 at 1:35 pm | Reply John William

Thanks a lot. I tried using the COLLATE keyword and it just did not work, I thought it wasn’t possible. Now I know that my problem was really just the syntax. LOL.
on August 12, 2010 at 9:20 pm | Reply Nitzan

Thanks you – you solved my problem and I am getting home to my kids…..
- on August 12, 2010 at 9:45 pm | Reply pinaldave
  
  This is the best comment I have ever received.
on August 20, 2010 at 2:18 pm | Reply Khalil

Hi Pinal,

I have the following error:

Can’t resolve collation conflict between “Latin1_GeneralCI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the union operation. Below is the SQL statement.

SELECT 2000000000 + ASCII(RIGHT(RDB_SETTING, 1)) AS ID, ‘.’ + LEFT(RDB_VALUE, 25) AS NAME, SUBSTRING(RDB_SETTING, 6, 1) AS PRIORITY, NULL
AS ANALYSIS, NULL AS RECOVERY, 1 AS RESTASK_CHANGE_COUNT, ‘UNAVAILABLE’ AS INCIDENT_ID, RDB_VALUE AS Remedy_Summary, NULL
AS ASSIGNEE, ‘Field Support’ AS Assignee_Group, ‘Commercial Ltd’ AS Company, NULL AS Contact, NULL AS Notes, NULL AS Address, NULL
AS PhoneNumber, ‘Busy’ AS Status
FROM dbo.RDB
WHERE (RDB_SECTION = ‘priority’) AND (RDB_SETTING LIKE ‘Alias%’) AND (RDB_SETTING ‘Alias’ + RDB_VALUE)
UNION
SELECT 2000000000 + Retain_ID AS ID, LEFT(Full_Name, 25) + ‘-UNAVAILABLE’ AS NAME, ’4′ AS PRIORITY, NULL AS ANALYSIS, NULL AS RECOVERY,
1 AS RESTASK_CHANGE_COUNT, ‘UNAVAILABLE’ AS INCIDENT_ID, ‘Planned Unavailable’ AS Remedy_Summary, LEFT(Full_Name, 25) AS ASSIGNEE,
‘Field Support’ AS Assignee_Group, ‘Commercial Ltd’ AS Company, NULL AS Contact, NULL AS Notes, NULL AS Address, NULL AS PhoneNumber,
‘Busy’ AS Status
FROM ARSystem.dbo.CTM_People
WHERE (Support_Staff = 0) AND (Client_Type BETWEEN 1000 AND 5999) AND (Assignment_Availability = 0) AND (Profile_Status = 1) AND
(Department = ‘Service’)
UNION
SELECT CAST(RIGHT(a.Task_ID, 12) AS int) AS ID, LEFT(a.Company + ‘-’ + a.RootRequestID + ‘-’ + a.Task_ID, 128) AS NAME, LEFT(a.Priority, 1) AS PRIORITY,
a.Retain_Analysis AS ANALYSIS, a.Retain_Recovery AS RECOVERY, a.RESTASK_CHANGE_COUNT, a.RootRequestID AS Incident_ID, LEFT(a.Summary,
254) AS remedy_summary, LEFT(a.Assignee, 50) AS Assignee, LEFT(a.Assignee_Group, 50) AS Assignee_Group, a.Location_Company AS COMPANY,
a.First_Name + ‘ ‘ + a.Last_Name AS CONTACT, CAST(a.Notes AS varchar(255)) AS Notes,
b.Street + ‘, ‘ + b.City + ‘, ‘ + b.State_Province + ‘, ‘ + b.Zip_Postal_Code AS ADDRESS, a.Customer_Phone_Number AS PhoneNumber,
a.zStatus AS Status
FROM ARSystem.dbo.TMS_Task AS a LEFT OUTER JOIN
ARSystem.dbo.SIT_Site AS b ON a.Site = b.Site
WHERE (a.Schedule_in_Retain = 1) AND (a.Status <= 6000) AND (a.StatusReasonSelection 3000 OR
a.StatusReasonSelection IS NULL)

Are you able assist with resolving this conflict issue, can the same rule be applied as it is a slightly different error message?

Many Thanks in Advance,

Khalil
on August 20, 2010 at 3:23 pm | Reply Khalil

Hi,

I have now fixed this, thanks for presenting the solution to me.

Best regards,

Khalil
on August 25, 2010 at 2:23 pm | Reply Fayaz Shaik

it really helped me a lot. Thank you sooooooooo much…
on September 10, 2010 at 7:13 pm | Reply Praveen Gundeti

Thank you soo much. Your post is really helped me. Once again thank you very much.

Regards,

Praveen
on September 14, 2010 at 2:14 pm | Reply Chan

Thanks a lot.. that’s a great help.
Chan
on September 24, 2010 at 2:37 pm | Reply Horizon

Thanku so much.. :)
on September 28, 2010 at 11:47 am | Reply Balaji

sorry I have a column which works under chinese collate I am getting this error

“Cannot resolve collation conflict between ‘Latin1_General_CI_AS_KS_WS’ and ‘Chinese_PRC_Stroke_CI_AI_KS_WS’ in equal to operation”

after using the COLLATE DATABASE_DEFAULT it returns the row count as zero even thoug there are some chinese values inside it.Is there any work around for this please help.

many thanks,
Balaji
on September 29, 2010 at 5:36 pm | Reply Lcy

Kardeş allah razı olsun ya :) Seni Seviyoruz..

Special Thanks
Thank You So Much. We Love You.
on September 30, 2010 at 1:22 am | Reply rohit

Hi

I am tring to create a view with tables from 2 different databases, these databases have different collation

i have used collate function but it gives me this error

Msg 447, Level 16, State 0, Line 1
Expression type int is invalid for COLLATE clause.

here is the select statement from my view

SELECT DOCTYPE,
DOCNUM,
GROSS = CAST(ABS(SUM(CASE WHEN NOMINAL = 45110 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
VAT = CAST(ABS(SUM(CASE WHEN NOMINAL = 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
NET = CAST(ABS(SUM(CASE WHEN NOMINAL != 45110 AND NOMINAL != 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2))
FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE LIKE ‘SL%’)
OR (DOCTYPE LIKE ‘Y%’)
GROUP BY
DOCTYPE,
DOCNUM
COLLATE SQL_Latin1_General_CP1_CI_AS
UNION
SELECT DOCTYPE,
DOCNUM,
GROSS = CAST(ABS(SUM(CASE WHEN NOMINAL = 45110 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
VAT = CAST(ABS(SUM(CASE WHEN NOMINAL = 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
NET = CAST(ABS(SUM(CASE WHEN NOMINAL != 45110 AND NOMINAL != 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2))
FROM Dreamliv.SQSDBA.D_DETAILS
WHERE (DOCTYPE LIKE ‘SL%’)
OR (DOCTYPE LIKE ‘Y%’)
GROUP BY
DOCTYPE,
DOCNUM

Please help.
on September 30, 2010 at 11:51 am | Reply Balaji

@All:
hii ppl I have resolved the chinese collation successfully. I use the Prefix N’ ‘ while inserting, updating and selecting values. By this way I am able to get the exact chinese characters without any issues.

Example:

SELECT * FROM Messages where Message=N’隐私权政策’
INSERT INTO Messages (Message) VALUES (N’隐私权政策’)

thanks to one and all!!
on October 8, 2010 at 4:10 pm | Reply gugu

It’s working, great job. :)

thx
on October 11, 2010 at 9:11 am | Reply bassy

its work
many thanks
on October 25, 2010 at 9:58 pm | Reply Dave

Also, when doing a UNION with tables from different databases/collation, note that the COLLATE properties of all text fields must match. To do this, add a COLLATE statement after each text field that is coming from the foreign table to the result set. UNION will then be able to compare text from each unioned query.

For example, if the foreign table has collation Latin1_General_CP1_CI_AS and the default database has SQL_Latin1_General_CP1_CI_AS, do this in the query of the foriegn table:

SELECT x.field COLLATE SQL_Latin1_General_CP1_CI_AS
FROM foreigntable x

Adding a COLLATE statement after each text field in the foreign table will ensure the UNION will be able to merge queries from disparate collating sequences.
on November 13, 2010 at 10:04 am | Reply Carlos

Thank you. I used this to create a view from different databases in SQL Server 2008
on November 29, 2010 at 10:44 am | Reply Ekkachai

its work
many thanks
on December 15, 2010 at 3:28 pm | Reply Loukas

Very good.

Thank you
on December 22, 2010 at 11:07 am | Reply Jayesh

Thanks dear it really solved my problem…
on December 28, 2010 at 2:46 pm | Reply zeeshan

Thanks a lot Dear,

You are always a great help. because ur examples are very streigtforward and to the point.

Thanks.
Regards,
Zeeshan.
on January 12, 2011 at 8:18 am | Reply monarco

hi,

i have a problem and says “Cannot resolve collation conflict for equal to operation”. i am using Windows Server 2008 R2 and a SQL server 2000. when i see the collation of instance it was Latin1…. but when i look the collation of databse it was only SQL_Latin1… how i can change the collation of instance because i need to change it into SQL_Latin1…?

i really need your help. thank you very much.
on January 17, 2011 at 5:12 pm | Reply Teja

I have a query as follows:
I have two tables, are of same design and residing in two different database server and of different collations

Can I use except & Intercept commands and to compare the results
For e.g..
Select field1 collate database_default, field2 collate database_default, field3 collate database_default….fieldn collate database_default from table1
except
Select field1 collate database_default, field2 collate database_default, field3 collate database_default….fieldn collate database_default from [dbserver].[dbname].dbo.table2
- on January 18, 2011 at 3:58 pm | Reply madhivanan
  
  Yes you can use those to find out a difference
on January 25, 2011 at 4:20 pm | Reply Jayesh

Please let me know where should i use Collation clause i m getting error in the select statement, Could not resolve collation conflict for column 1 in the above query.

SELECT
SalesChannel,
NoOfTransactions,
TotalTickets,
GrossAmount
FROM(
SELECT
tra.new_saleschannelidname SalesChannel,
count(tra.new_transactionid) As NoOfTransactions,
sum(tra.new_numberoftickets) As TotalTickets,
sum(tra.new_grossticketvalue) As GrossAmount
FROM NEWTRANS tra
WHERE tra.deletionstatecode = 0 and new_saleschannelidname is not null
and tra.new_vistabookingstatus = ‘P’
–and tra.new_bookingcommitted = 1–’Yes’
–and tra.new_paymentprocessed = 1–’Yes’
and tra.new_saleschannelidname not like ‘LOYALTY%’
and CAST(Convert(CHAR(10),
dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) >= ‘{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) = ‘{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,itmtra.new_transactiondate),121)AS SMALLDATETIME) =’{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) <='{To_Date}'
–and DATENAME(yyyy,dateadd(mi,330,tra.new_transactiondatetime)) = DATENAME (yyyy,getdate())
GROUP BY tra.new_saleschannelidname
)tra
ORDER BY SalesChannel
on March 5, 2011 at 2:00 pm | Reply Arash

Dear Jeffrey

The solution you have introduced solved my problem completely.
Thank you for it

SELECT DISTINCT col1
FROM sale
WHERE (col2 IN
(SELECT id COLLATE DATABASE_DEFAULT
FROM linkedServer.DB1.dbo.tbl1))
on March 7, 2011 at 6:24 pm | Reply Muratcan Karakört

Thnaks so much :))))))))))))))))))))))))))))
on April 15, 2011 at 5:47 am | Reply Bill Ritchie

I know nothing about databases and SQL servers. I have a biometric time clock for my business and in the course of trying to install it on a new computer(I have previously installed it on at least 3 other computers) I got this error message-
“Cannot resolve collation conflict for equal to operation”. The company that sells the time clock says that the problem is with the SQL server and they can’t help me. I read the first post but it assumes you have a clue and I don’t. Would someone be able to walk me through this? I build and repair computers for myself and my business and install and use lots of software but this is way over my head. I am hesitant to even ask about a tutorial because it seems to me that it would take forever to learn enough to be able to figure this out. Any suggestions would be greatly appreciated.

Thanks for looking, Bill
on April 17, 2011 at 7:45 pm | Reply FAH

Was the first hit on Google and solved my problem. Thanks a lot!
on April 21, 2011 at 2:37 pm | Reply Sridhar

It really helped me. Thanks for your help
on May 7, 2011 at 2:21 pm | Reply Ashitha

Thanks……These all really helpful.
- on May 7, 2011 at 2:26 pm | Reply Ashitha
  
  Thanks
on May 7, 2011 at 2:25 pm | Reply Ashitha

delete from procstatus with (rowlock) where
empcd in(select m.empcd from procstatus m with (nolock)
inner join billcontrol b with (nolock) on m.updatedby =b.preparedby and m.updatedon=b.preparedon and b.cancelled =’Y’ ” where(m.updatedby = procstatus.updatedby And m.updatedon = procstatus.updatedon))”

While executing this query this error occuring.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Pls Help………
on May 9, 2011 at 3:10 pm | Reply Guido Maes

I love you Pinal Dave!
on May 25, 2011 at 8:12 pm | Reply Feodor

Pinal,

it is true that this would be one way to solve the failing query, but the questions is: why are the columns with different collations? Is this a design feature, or is it a mistake?
And if we change the collation of the columns ‘on the fly’ in our query, then are we going to get the correct resultset?

Think about it – the collation is a set of rules by which the data is treated (recognized, sorted, distinguished) and if we force the query to make the collations of the join collumns equal, then will this affect the resultset?

Check this article, and let me know that you think: http://sqlconcept.com/2011/05/25/collation-concept-sql-server-database-column/

Feodor
on May 26, 2011 at 5:34 pm | Reply Deon

Thank you, works nicely
on May 27, 2011 at 1:21 am | Reply Feodor

And here is a good example why COLLATE database_default is NOT a good solution to the collation conflict problem: http://sqlconcept.com/2011/05/26/beware-collation-database_default/
on June 2, 2011 at 3:32 pm | Reply simon

thank you for using simple examples. two answers in two days!
on July 13, 2011 at 3:13 pm | Reply Pradeep

thankuuuuuuuuuuuuuuuuuuuuuu………….. its nice
on July 14, 2011 at 11:06 pm | Reply Shyam

I had a query with a union of tables both of which are from the same collation. Worked fine in 2005. Now that I have migrated to 2008, the same query is failing. Any ideas?
- on July 18, 2011 at 7:29 pm | Reply madhivanan
  
  What was the error you got?
on July 15, 2011 at 1:04 am | Reply Forrest Pugh

I would like to add that while this may work on a single server with heterogeneously collated databases, it *may not* work for all collation regimes, or across servers with different collation regimes selected as the default type.

In this case it is possible to generate a maintenance script that combs through the various DBs and Tables on a server and alters the collation type of the text type data contained therein.

In this scenario it can take some time to correct the issue either by updating the existing tables (expensive transaction), or by creating a backup version of the existing tables, and scripting them to be recreated with the correct collation, then reinserting the table content into the new correct table (cheaper transactions individually, but may add up to be more costly than an update).

The better solution would be to change the default collation on the odd server, which requires some down time as well.

Plan accordingly. Our scenario involves a single server collated one way with all the others collated (uniformly) in a different regime. Unfortunately this server is a primary reference and we cannot afford the downtime to correct it… we run the maintenance procedure over night to correct any new table content created that day, which is fairly effective for us.
on August 3, 2011 at 6:34 pm | Reply morìs

It works.
Thank you very much.
on August 13, 2011 at 4:08 pm | Reply Pargat Singh Randhawa

Wow!! Great, this really helps me a lot…
on August 18, 2011 at 11:32 am | Reply Savitha

Even I am facing a error like this: Cannot resolve collation conflict for column 1 in SELECT statement.
My querry is SET @allItems = ‘SELECT CAST(iu.item_id AS varchar(max))+”/”+iu.description+”/I”+”/”+CAST(it.sequence AS varchar(max))+”/”+it.item_feature_parents as itemVal FROM ifl_item_u_Syn as iu INNER JOIN
#item_ids as it ON it.item_id= iu.item_id
on September 9, 2011 at 7:26 pm | Reply Penny

Sorry, Im confused….

I have taken a working SP from 2005 into 2008. Made no changes.

I have the problem between a tempory table and a function call in the ON clause of a JOIN.
Now the function was created in the database, and so was the tempory table, so although your solution works, I dont understand where the problem has come from.
Please can you explain how two things created in the same database can get this problem? Thanks
on September 26, 2011 at 5:19 pm | Reply Velmurugan

thanks a lot…. it works…….
on October 5, 2011 at 1:12 am | Reply Raghav

Didn’t get that?
on October 13, 2011 at 5:42 pm | Reply Danny

I just ran in to this collation issue during a data transfer. My Google search had this “old” article as the top result.

Great article, got me out of a jam. Thank you very much!
on October 16, 2011 at 3:58 pm | Reply Nowafl M

thank you very much
on October 21, 2011 at 4:18 pm | Reply Chat odasi

hi Ilike it very much, I hope you will soon update your work!thanks for sharing
on December 14, 2011 at 2:45 am | Reply Bryon Jordan

Thanks so much!
on December 21, 2011 at 3:05 am | Reply osssman

tank so much man:)
on December 21, 2011 at 8:08 pm | Reply bigdave

Enormous!
on January 8, 2012 at 7:00 am | Reply SQL SERVER – Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 « SQL Server Journey with SQL Authority

[...] and Answers ISBN: 1466405643 Page#46-47 Find Collation of Database and Table Column Using T-SQL SQL SERVER – Cannot resolve collation conflict for equal to operation Find Database Collation Using T-SQL and SSMS Change Collation of Database Column – T-SQL Script [...]
on January 31, 2012 at 4:23 pm | Reply Oscar

Thanks very much.
on February 2, 2012 at 2:30 pm | Reply pardhu

Thanks u so much man :)
on February 16, 2012 at 5:52 pm | Reply ABC

Hi,

I have the following query –

CREATE VIEW MappedObjects
AS
SELECT
EM.EntityID AS ID,
EM.EntityType AS EntityType,
ParentEntityID AS ParentID,
EM.EntityGUID AS GUID, EM.[Name] ,
ES.StoreID AS StoreID
FROM EntityMaster AS EM WITH (NOLOCK) LEFT JOIN EntityStore AS ES WITH (NOLOCK) ON ES.EntityID = EM.EntityID AND ES.EntityType COLLATE DATABASE_DEFAULT = EM.EntityType COLLATE DATABASE_DEFAULT
UNION ALL
SELECT TP.TopicID AS ID, ‘Topic’ AS EntityType,0 AS ParentID, TP.TopicGUID AS GUID, TP.[Name], TS.StoreID AS StoreID
FROM Topic AS TP WITH (NOLOCK) LEFT JOIN StoreMappingView AS TS WITH (NOLOCK)
ON TS.EntityID = TP.TopicID AND TS.EntityType =’Topic’
UNION ALL
SELECT NW.NewsID AS ID,’News’ AS EntityType,0 AS ParentID, NW.NewsGUID AS GUID, NW.Headline AS [Name], NS.StoreID AS StoreID
FROM News AS NW LEFT JOIN StoreMappingView AS NS WITH (NOLOCK)
ON NS.EntityID = NW.NewsID AND NS.EntityType =’News’
UNION ALL
SELECT PR.ProductID AS ID, ‘Product’ AS EntityType,0 AS ParentID, PR.ProductGUID AS GUID, PR.[Name], PS.StoreID AS StoreID
FROM Product AS PR LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON PR.ProductID = PS.EntityID AND PS.EntityType =’Product’
UNION ALL
SELECT CP.CouponID AS ID, ‘Coupon’ AS EntityType,0 AS ParentID, CP.CouponGUID AS GUID, CP.[CouponCode] AS [Name], PS.StoreID AS StoreID
FROM Coupon AS CP LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON CP.CouponID = PS.EntityID AND PS.EntityType =’Coupon’
UNION ALL
SELECT OO.OrderOptionID AS ID, ‘OrderOption’ AS EntityType,0 AS ParentID, OO.OrderOptionGUID AS GUID, OO.[Name], PS.StoreID AS StoreID
FROM OrderOption AS OO LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON OO.OrderOptionID = PS.EntityID AND PS.EntityType = ‘OrderOption’
UNION ALL
SELECT GC.GiftCardID AS ID, ‘GiftCard’ AS EntityType,0 AS ParentID, GC.GiftCardGUID AS GUID, GC.SerialNumber AS [Name], PS.StoreID AS StoreID
FROM GiftCard AS GC LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON GC.GiftCardID = PS.EntityID AND PS.EntityType = ‘GiftCard’

It gives me an error as -
Msg 451, Level 16, State 1, Procedure MappedObjects, Line 4
Cannot resolve collation conflict for column 5 in SELECT statement.

Please advise!!
on February 21, 2012 at 9:19 am | Reply Amit

I get collation error
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Japanese_BIN” in the equal to operation.

on my below query

declare @var varchar(max)
select @var = ‘Exec sp_change_users_login’
+ ”” + ‘auto_fix’+””+’,'+””+ name+”” +char(13)+ coalesce(@var,”)
from sysusers where name in
(select name from master..syslogins) order by name
Print @var
Exec(@var)
GO
on February 22, 2012 at 5:32 pm | Reply Dougal

Couldn’t get head around how to resolve the conflict in

select code from suppliers where suppliercode in (select code from #tempsuppliers)

tried

select code collate database _default from suppliers where suppliercode in (select code from #tempsuppliers collate database_default)

…but that just threw a syntax error. after a flash of inspiration realised that:

select code collate database _default from suppliers where suppliercode in (select code collate database_default from #tempsuppliers )

was the fix, this worked a treat, so thanks for all your tips!
on February 27, 2012 at 8:32 pm | Reply Radityo Ardi

You sick, maaaan! I know you’re the SQL Master!
What SQL problems which you can’t resolve?
even this “simple but iritating” problem, you know the answer! Good job!
on March 1, 2012 at 4:14 pm | Reply robsib

It works nice also in INNER JOINS in ON clause
Thanx
on March 7, 2012 at 2:40 am | Reply texeyesrady

Thanks for posting this! You saved me hours of frustration and research. :)
on March 14, 2012 at 3:10 pm | Reply Sahhnawaz

Thanks pinnal,
This solution save my lot of time and job..
Tahnk you once again
on March 16, 2012 at 12:06 pm | Reply Sangamesh

How to resolve the collation problem in the update query?
on March 22, 2012 at 9:18 am | Reply URVISH SUTHAR

Thanks 4 this….
on March 22, 2012 at 10:53 pm | Reply augusto c

thanks, this really works
on March 31, 2012 at 3:33 am | Reply Alan

Another Pinaliscious solution. Thanks!
on April 26, 2012 at 3:54 am | Reply Fritze

Thanks for your tipp! It saved my day.
on April 27, 2012 at 5:08 pm | Reply Marvin Schmidt

Hey Pinal

I works, but i have issues with danisk special characters like æ ø å they get converted to char like ‘ > †

the databases are using Danish_Norwegian_CI_AS SQL 2008 and the other is SQL_Scandinavian_CP850_CI_AS SQL 2005

Can you help…
on May 7, 2012 at 9:23 pm | Reply Elizabeth

Easiest online solution I’ve ever found. Thanks!
on May 8, 2012 at 3:46 pm | Reply mohan

thanx
on May 15, 2012 at 6:10 pm | Reply Paulo

That was really helpful! Thanks a lot!
on May 29, 2012 at 12:24 pm | Reply Giuseppe Knezovich

You made some nice points there. I looked on the internet for the subject and found most people will consent with your blog.
on June 6, 2012 at 9:59 pm | Reply Paulo Cornélio

superb
on June 9, 2012 at 12:07 am | Reply glutenfreesql

Thanks
on June 11, 2012 at 5:24 pm | Reply rajkumar

Great…….It worked for me.
on June 12, 2012 at 9:41 pm | Reply marcoa_escalante

excellent !!
on June 18, 2012 at 11:23 am | Reply jibson

Hey pinal,

Thanks for the solution
on June 19, 2012 at 7:58 pm | Reply Dejan Miklavcic

You are a genious :D !!!!!
on June 30, 2012 at 4:28 pm | Reply Nitin Vijay

Hello Pinal,

Thanks for the Solution….
on July 12, 2012 at 7:00 am | Reply SQL SERVER – Effect of Case Sensitive Collation on Resultset « SQL Server Journey with SQL Authority

[...] effect of the workaround. Collation is a very deep subject. Earlier I wrote an article how one can resolve the collation error when different collation values are compared. Today in most simple way I would like to explain that [...]
on July 13, 2012 at 1:15 am | Reply GC Koch

Re: Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “German_PhoneBook_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

It may be of interest the “best practice” numeric sql construct (also on joins) avoids collation conflicts between user-defined and system databases. Example: the following numeric “in” statement is successful between the German_PhoneBook_CI_AS and SQL_Latin1_General_CP1_CI_AS collations:

select DatabaseName from VersionDef
where VersionCode = N’10.0.1.4540′
and DatabaseSID in (
select database_id
from sys.databases
where name = DB_NAME()
)
on July 26, 2012 at 11:50 am | Reply Manoj kumar

Hi All,
One of our mappings in ODI generating code which will hit MS Sql 2008. I am getting the same error. In my case, I can not change the code generated by ODI tool. I have to make sure that the two tables/columns have the same collation.
How can i achieve that without interrupting any other databases/data ?
Could you help asap.
on August 17, 2012 at 11:28 am | Reply TUSHAR RANE

IA AM GETTING ERROR WHILE RUN FOLLOWING STATEMENT

UPDATE Kept_Report_Master
SET [DESCRIPTION]=DISP_TAB.[DESCRIPTION],
DISP_CLASS=DISP_TAB.CLASS
FROM Kept_Report_Master
INNER JOIN DISP_TAB
WHERE Kept_Report_Master.DISPOSITION_CODE = DISP_TAB.DISP_CODE
on August 23, 2012 at 12:37 am | Reply michele

Thank you very much. You save my time again!!
on August 24, 2012 at 2:14 pm | Reply Alex

Thanks very much!
on September 5, 2012 at 6:53 pm | Reply Maria J. Santos

Thank you. Your help is always apreciated :)
on September 19, 2012 at 7:01 am | Reply SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

[...] Cannot resolve collation conflict for equal to operation [...]
on September 20, 2012 at 3:04 pm | Reply sanitabh soni

Thank You, It was very informative.
on September 26, 2012 at 2:20 pm | Reply Alastair Vize

Pinal Dave – You are an international treasure. Once again you get me out of a tight spot with a quick, reliable and straightforward solution. I am (and not for the first time) very much in your debt. Abundant thanks.
on September 28, 2012 at 10:13 am | Reply Khang Huynh Duy

thank you very muck!
good luck!
on October 8, 2012 at 12:53 pm | Reply Moin

Thanks for this wonderful trick….
on October 21, 2012 at 4:58 pm | Reply sohbet

thank you broo
on November 8, 2012 at 11:07 am | Reply muthukumar

Dear sir,
i am muthukumar..i have a error when restoring databae in sql server 2005.And i hava taken the database backup in sql2008.
The error message is
The media family on device ‘G:\myproject\Insurance.BAK’ is incorrectly formed. SQL Server cannot process this media family.
VERIFY DATABASE is terminating abnormally.
please help me…
- on February 15, 2013 at 7:17 pm | Reply madhivanan
  
  This can not be restored. You can restore the backup that were taken by earlier or current versions only
on December 30, 2012 at 1:02 pm | Reply Iliia Shterev

There is very little to say after Alastair Vize’s “Pinal Dave – You are an international treasure.” May be only THANK YOU.

Pinal please help me simplify

In ragards to XXX_CI and XXX_CS do I undestand correctly that with XXX_CI “Three = ThREE” would be true. Respevtively with XXX_CS “three = ThRee” it would be false and only “Three = Three” it would be true?
Also is this true in the cases of IF compare, Where clause, Join predicates etc.
- on February 15, 2013 at 7:20 pm | Reply madhivanan
  
  If the collation is XXX_CS, then “three=”ThRee” becomes false. This is application if you compare with anystring with that collation in IF statement also
  - on February 19, 2013 at 4:57 pm | Reply Iliia Shterev
    
    madhivanan – Thank youuuuuu. I could have tried to see it in practice but I would never be sure if this makes it a rule
on January 9, 2013 at 4:55 pm | Reply Prem

Thanks a lot!!!
on January 12, 2013 at 8:35 am | Reply Iliia Shterev

Pinal, Please, please take a look at my question from Dec 30. and see if you can help me. You can just use yes or no 3 times and that would be all.
on January 29, 2013 at 7:47 pm | Reply Nazim Akber Ali

Thanks It works for me
on February 25, 2013 at 1:58 pm | Reply Çağlar Durgun

Great, many thanks!!
on March 27, 2013 at 2:33 am | Reply Oscar

Pinal thank you very much, it really works, not just for each field, but it also works simply adding like a sentence at the end of the query. Greettings from Caracas-Venezuela
on April 3, 2013 at 7:01 am | Reply SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

[...] Cannot resolve collation conflict for equal to operation [...]
on May 15, 2013 at 2:36 pm | Reply Lakshmi Kiranmayi Y

Thanks it worked.
on May 16, 2013 at 8:48 pm | Reply Pradip killedar

Thanks…

Pradip killedar
Kolhapur