Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Unanswered: Cannot resolve collation - ERROR!

    Hello all,

    I seem to get the following error when I join two tables from two separate databases.

    [[Cannot resolve collation conflict for equal to operation.]]


    Using

    SELECT DATABASEPROPERTYEX('AAA', 'COLLATION'),
    SELECT DATABASEPROPERTYEX('BBB', 'COLLATION')

    returns exactly the same answer therefore the default collation order in both databases is the same. Why do I still get this error then?!

    I know that you can change it for each individual table but isn't there a better solution?

    Your help would be appreciated.
    Shadow to Light

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database ..... ALTER TABLE... refer to books online for more information.

    If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Originally posted by Satya
    Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database ..... ALTER TABLE... refer to books online for more information.

    If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.
    I am using SQL Server 2000.

    The problem is that altering the database to set the collation order to be the same as the other one is what I have done but I still get the same error.

    I am not keen on altering the collation order for every table/column hence my original question on this forum.

    Your help would be appreciated.
    Shadow to Light

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to this DBJournal article for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    hi,

    you might specify the collation directly like this

    :
    join table on fieldname COLLATE ... = fieldname COLLATE ...
    :
    and use the same collation for both fields

    or have a look at bol "COLLATE"

    markus

  6. #6
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Originally posted by msieben
    hi,

    you might specify the collation directly like this

    :
    join table on fieldname COLLATE ... = fieldname COLLATE ...
    :
    and use the same collation for both fields

    or have a look at bol "COLLATE"

    markus
    Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.

    I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!
    Shadow to Light

  7. #7
    Join Date
    Dec 2002
    Posts
    29
    Originally posted by Crespo-n00b
    Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.

    I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!

    Probably, we could help you more if you'll post the JOIN in the forum.
    I got similar error when in a stored procedure I've created a "Table" type variable and used it in a join... all this happened in one database. The problem was solved by creating the "Table" type variable with the current database's collation order.

    Hope this helps!

    Best regards,
    Botond

Posting Permissions

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