Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Probs with INNER JOIN Statement

    Hi folks,

    I got a strange Problem with this statement:

    select * from [db1].[dbo].[table1] AS db1
    INNER JOIN [db2].[dbo].[table1] AS db2
    ON (db1.Text = db2.Text)

    Text fields are both nvarchar(50)

    I think this should work, but it doesnt?
    I got a SQL Server Error 446

    I know, text compares are not that good, but it exists no other way....

    Thanks for any advice!!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to use COLLATE to resolve the collation conflict.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    How does my statement looks like with COLLATE?!?

    Sorry, but im a n00b on database development, i got my skills more in C++ as in SQL :-P

  4. #4
    Join Date
    Apr 2004
    Posts
    6
    ok, i got it.....

    Thanks a lot :-D

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why do your boxes have different collations...

    Just curious....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2004
    Posts
    6
    I checked it again, both fields are nvarchar() but one have size 50 the other 30, can this be the Problem (I used to test CONVERT(nvarchar(30), blah) on the bigger field), still got collation's *hmmm*

    nvarchar, because its multilanguage ;-)

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by AegeriAndi
    I checked it again, both fields are nvarchar() but one have size 50 the other 30, can this be the Problem (I used to test CONVERT(nvarchar(30), blah) on the bigger field), still got collation's *hmmm*

    nvarchar, because its multilanguage ;-)
    Nope! With VARCHAR columns, size doesn't matter (no comments from the peanut gallery, please)!

    The problem is that whoever designed the tables specified that they wanted the data to be collated (sorted and compared) using different rules. SQL Server can deal with each column differently if the data modeler chooses (which often makes the users go insane, so its worth it just for the fun that it causes)!

    -PatP

  8. #8
    Join Date
    Apr 2004
    Posts
    6
    The problem is that whoever designed the tables specified that they wanted the data to be collated (sorted and compared) using different rules. SQL Server can deal with each column differently if the data modeler chooses (which often makes the users go insane, so its worth it just for the fun that it causes)!
    How can i see in Enterprise Manager if especialy for that column a special collation is set? if no one is set, DB-Stand should be used, right? And this is for both databases the same: Latin1_General_CI_AS ;-)

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't often use Enterprise Mangler, so I had to go look!

    1) Open the SQL Server
    2) Open the Databases collection
    3) Open the appropriate database
    4) Open the tables collection
    5) Open the table of interest
    6) Click on the column of interest
    7) In the lower left corner, check the Collation at the bottom of the Columns tab.

    -PatP

  10. #10
    Join Date
    Apr 2004
    Posts
    6
    ok, got it, this field has a collation set to: German_PhoneBook_CI_AS, i think i have to cut some fingers :-D

    Thanks a lot for your help!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wait until you get some confused newbie that sets the collation to Arabic for a Cyrillic column! The looks on the users faces as they try to make sense of it can be hysterical.

    On a more serious note, there might be a good reason for picking a specific collation. More often than not it is random chance, but there can still be good reasons.

    Another observation, if the collation causes you great grief you can also have one collation in the table itself, but use a different collation in the ORDER BY clause. This is great for both the dba and the user, but it can wreak havok on the unwary developer.

    -PatP

Posting Permissions

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