Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Colation problem

    Hi,
    I am using SQLServer 2000 with SP4 and I am getting a strange collation problem.
    I have three tables, TEmployee, TMechanic and TManager, each with the two columns Firstname, Lastname which are both varchars.

    I run the following query:
    <PRE>
    SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM ((
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM
    TEmployee query1 WHERE FIRSTNAME = 'John'
    UNION ALL
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM
    TCoManager query1 WHERE FIRSTNAME = 'John'
    UNION ALL
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM TMechanic query1 WHERE FIRSTNAME = 'John')
    UNION
    (SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
    FROM TEmployee query2 WHERE FIRSTNAME = 'Michael'
    UNION ALL
    SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
    FROM
    TCoManager query2 WHERE FIRSTNAME = 'Michael'
    UNION ALL
    SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
    )) OUTERUNION

    </PRE>

    I get the following error:
    Cannot resolve collation conflict for column 2 in SELECT statement.

    If I change my select statement to only have one column (doesn't matter which column) it doesn't happen.

    Any ideas, greatly appreciated?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am betting the collations on the columns comprising your union query do not match. when someone created one of these tables they may have specified some column level collations. I think you are going to have to export the data from the problematic table recreate your table with the write collations and then reimport your data. However this may have been done for a reason. Got documentation?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    Hi Thrasymachus,
    Thanks for getting back to me.
    I have looked at the scripts and they look ok, they are just simply create table statements with n oreference to collation.
    So I need to know what does SQLServer set them to, or what does it think they are?
    Ok this sounds like a stupid question so forgive, how do I check the collation levels for the columns?
    What's interesting is a similar query works fine:

    SELECT OUTERUNION.FIRSTNAME, OUTERUNION.LASTNAME FROM (
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM
    TEmployee query1 WHERE FIRSTNAME = 'John'
    UNION ALL
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM
    TCoManager query1 WHERE FIRSTNAME = 'John'
    UNION ALL
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
    FROM TMechanic query1 WHERE FIRSTNAME = 'John'
    UNION ALL
    SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
    FROM TPerson query2 WHERE FIRSTNAME = 'Michael'
    UNION ALL
    SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
    FROM
    TCoManager query2 WHERE FIRSTNAME = 'Michael'
    UNION ALL
    SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME FROM TMechanic query2 WHERE FIRSTNAME = 'Michael'
    ) OUTERUNION

    Note the differences are:
    I have changed the "Union" keyword in the middle to a "Union All" and removed inner brackets.

    Any help greatly appreciated.

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    execute sp_help sp in both table and check the difference.
    Code:
    exec sp_help tablename
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Jan 2006
    Posts
    119
    I have checked the collations, of all the tables, they are all Latin1_General_CI_AS. I think the problem is because SQLServer decides to change the collation in one of the sub selects. As the problem doesn't happen if I limit the number of nested sub selects to one.
    Does anybody know how I get the default collation for a database, i.e. not just the collation for a column / table?
    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT DatabasePropertyEx('master', 'Collation')
    -PatP

  7. #7
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    I wonder if the problem might be with tempdb... is the default collation for tempdb different from your user database ? SQL might be storing intermediate data in tempdb, particularly if you are using UNION vs. UNION ALL.

    Bill

  8. #8
    Join Date
    May 2012
    Posts
    1

    Talking Fixed the Collation problem.

    I had a similar problem where:
    1. Two databases were in use. Both same collation.
    2. All tables in play were of the same collation.
    3. The SP UNION statement was clearly causing the problem.

    Bill's response [Six years ago!] SOLVED it for me.

    So, whilst it's been a while since he made his response.. THANKS.

    PS. Stopping the SQL Server, copying the model database files, starting SQL, attaching the renamed files, changing the collation, stopping the SQL Again, moving [carefully] the original model files, then renaming the copied model files to the original names and starting the SQL Server again did the trick.
    TempDB takes its collation from the model db.
    Ref: http://blogs.msdn.com/b/john_daskala...4/9174867.aspx (thanks to Jon for this).


    Thanks again.
    D.
    Last edited by TheCtosian; 05-17-12 at 09:06. Reason: provide reference to solution provider

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use SSMS to execute just the
    Code:
    SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
       FROM TEmployee query1
       WHERE FIRSTNAME = 'John' 
    UNION ALL SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
       FROM TCoManager query1
       WHERE FIRSTNAME = 'John' 
    UNION ALL SELECT QUERY1.FIRSTNAME, QUERY1.LASTNAME
       FROM TMechanic query1
       WHERE FIRSTNAME = 'John' 
    UNION ALL SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
       FROM TPerson query2
       WHERE FIRSTNAME = 'Michael' 
    UNION ALL SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
       FROM TCoManager query2
       WHERE FIRSTNAME = 'Michael' 
    UNION ALL SELECT QUERY2.FIRSTNAME, QUERY2.LASTNAME
       FROM TMechanic query2
       WHERE FIRSTNAME = 'Michael'
    See what line or lines SSMS complains about.

    There's a brute force, simple fix that will supress this particular error but you need to see where the problem lies in your schema. You may or may not choose to fix it, but you need to know what and where the problem is!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •