SQL SERVER – Cannot resolve collation conflict for equal to operation

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

357 thoughts on “SQL SERVER – Cannot resolve collation conflict for equal to operation

  1. 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.

  2. 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?

    • Put the ‘Collate Database_Default’ in front of the column names. Like Column _name Collate Database_Default. The error will be resolved.

  3. 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

  4. 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

  5. Pingback: SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS Journey to SQL Authority with Pinal Dave

  6. 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.

  7. 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.

  8. 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

  9. 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…

  10. 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

  11. 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

  12. Hi dave….awesome….a solution made to look so simple for quite a complex problem (it was definetly complex for me…:)…thanks a ton

  13. 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

  14. 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.

  15. 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

  16. 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

  17. Thanks !!!

    This help me with this …

    WHERE ESTADO_PROCESO COLLATE DATABASE_DEFAULT in (select string from dbo.fn_SplitStrings(@as_estados, ‘,’))

  18. 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

  19. Pingback: SQL SERVER - Change Collation of Database Column - T-SQL Script Journey to SQL Authority with Pinal Dave

  20. 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

  21. 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?

  22. 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

  23. 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

  24. 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.

  25. 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.

  26. 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

  27. 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

  28. Thanks Pinalkumar, worked a treat for me.

    Thank God for forums and the people like you who give their expertise so freely and unselfishly.

  29. 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!

  30. 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.

  31. 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

  32. 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

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

    Select accountID from Table1
    where AccountID in (select accountID from table2)

  34. Anu this worked fine for me:

    “WHERE accountID COLLATE DATABASE_DEFAULT IN
    (SELECT accountID COLLATE DATABASE_DEFAULT
    FROM table2)”

  35. 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..?

  36. 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…

  37. 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

  38. 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….

  39. 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′

  40. 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

  41. 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’

  42. 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

  43. Pingback: SQL SERVER – Several Readers Questions and Readers Answers Journey to SQL Authority with Pinal Dave

  44. 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

  45. 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?

  46. 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!

  47. @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

  48. 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

  49. 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.

  50. 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.

  51. 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

  52. 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

  53. 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.

  54. @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!!

  55. 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.

  56. Thanks a lot Dear,

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

    Thanks.
    Regards,
    Zeeshan.

  57. 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.

  58. 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

  59. 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

  60. 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))

  61. 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

  62. 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………

  63. 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

  64. 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?

  65. 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.

  66. 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

  67. 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

  68. 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!

  69. Pingback: SQL SERVER – Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 « SQL Server Journey with SQL Authority

  70. 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!!

  71. 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

  72. 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!

  73. 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…

  74. Pingback: SQL SERVER – Effect of Case Sensitive Collation on Resultset « SQL Server Journey with SQL Authority

  75. 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()
    )

  76. 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.

  77. 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

  78. Pingback: SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

  79. 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.

  80. 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…

  81. 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.

  82. 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.

  83. 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

  84. Pingback: SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

  85. Hi pinaldave,

    Am getting the error which mentioned below while executing some plsql,

    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the UNION operation.

    So, what to do?. Please help me..

  86. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

  87. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  88. The !funny thing about SQL Server is that it reports collation error even if statements like this one:

    IF @recId IS NULL AND @labelId IS NULL

  89. i have a procedure with create temp table using with common table expression . then i insert record into temp table. after that i update one table. procedure contains so many parameters. Same procedure call from different form from vb.net not given error but in one form it gives error below.
    Cannot resolve the collation conflict between “Japanese_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

    Please give solution.

  90. Pinal, how do i resolve this error in the following update statement

    update DW_Store set Appts = (select count(AppointmentID) from sdsdb.dbo.Appointments inner join sdsdb.dbo.stores on sdsdb.dbo.Appointments.StoreID = sdsdb.dbo.stores.StoreID where sdsdb.dbo.stores.StoreReference = bi.dbo.DW_Store.Store and sdsdb.dbo.Appointments.AppointmentStartDateTime collate SQL_Latin1_General_CP1_CI_AS = bi.dbo.DW_Store.Date collate Latin1_General_CI_AS)

  91. ALTER PROCEDURE [dbo].[usp_Rep_CMSReporting_CIRelationshipViewnew]
    @CINAME VARCHAR(Max)
    AS
    BEGIN
    CREATE TABLE #HIERARCHY(PARENT VARCHAR(MAX),
    CHILD VARCHAR(MAX),
    PARENT1 VARCHAR(MAX),
    CHILD1 VARCHAR(MAX))
    INSERT INTO #HIERARCHY (PARENT,CHILD,PARENT1,CHILD1)
    SELECT CI.CIName + ‘ (‘ + ciClient.CIName + ‘) ‘ + ‘ ‘ + CI.CIDescription As PARENT ,CI1.CIName + ‘ (‘ + ciClient1.CIName + ‘) ‘ + ‘ ‘ + CI1.CIDescription AS CHILD,ci.CIName as parent1,ci1.CIName as child1 FROM AssociatedCI ASSOC
    INNER JOIN CIMASTER CI ON CI.ID = ASSOC.CIMasterID
    JOIN CIOrganisationMap ciom on CI.ID = ciom.CIID
    JOIN CIMaster ciClient on ciom.OrganisationID = ciClient.ID
    INNER JOIN CITYPE CT ON CI.CITypeID = CT.CITypeID
    INNER JOIN CIMaster CI1 ON CI1.ID = ASSOC.AssociatedCIID
    JOIN CIOrganisationMap ciom1 on CI1.ID = ciom1.CIID
    JOIN CIMaster ciClient1 on ciom.OrganisationID = ciClient1.ID
    INNER JOIN CITYPE CT1 ON CI1.CITypeID = CT1.CITypeID
    WHERE ciClient.ID NOT IN(81) and ciClient1.ID NOT in (81)

    declare @t table (parent varchar(max), child varchar(max), lev int, fullpath varchar(max),parent1 varchar(max), child1 varchar(max))
    declare @lev int
    set @lev = 0

    –Get Root node(s)
    insert @t (parent, child, lev, fullpath,parent1,child1)
    select distinct null, p.parent, @lev, p.parent,null,p.PARENT1
    from #HIERARCHY p
    left join #HIERARCHY c on c.child = p.parent
    where c.child is null

    while @@rowcount > 0
    begin
    set @lev = @lev + 1

    –Get all children of current level
    insert @t (parent, child, lev, fullpath,parent1,child1)
    select h.parent, h.child, @lev, t.fullpath + ‘ –> ‘ + h.child,h.PARENT1,h.CHILD1
    from @t t
    join #HIERARCHY h on h.parent = t.child and t.lev = @lev-1

    –make sure a circular reference doesn’t put is in an infinate loop
    left join @t x on x.parent = h.parent and x.child = h.child
    where x.parent is null
    end
    –print ‘helper table’
    –select * from @t order by fullpath

    select Distinct replicate( ‘–>’, lev)+ ‘ ‘ + child AS ‘Relationship View’,fullpath,lev,parent1,child1 from @t
    where parent1 like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’ or child like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’ or fullpath like ‘%’ + ISNULL(@CINAME, parent1) + ‘%’
    order by fullpath
    –order by lev,child
    drop table #hierarchy
    END

    • Msg 468, Level 16, State 9, Procedure usp_Rep_CMSReporting_CIRelationshipViewnew, Line 43
      Cannot resolve the collation conflict between “Latin1_General_CI_AS_KS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s