Post reply

How to handle Collation errors when joining 2 tables of different databases

  • June 18, 2009 at 1:37 am

    #133013

    Hi All,

    I have 2 databases with different collations, when i try to join them it shows me the collation error please help in soloving the error.

    Thanks in advance

  • June 18, 2009 at 2:05 am

    #1011507

    can you post the code which you are using, so some one can have a look and suggest some option

  • June 18, 2009 at 2:51 am

    #1011526

    My Query is

    Select B.location, A.loccvg from [db1].[dbo].[dummy1] A

    inner join [db1].[dbo].[dummy1] B on A.LOCATION=B.LOCATION

  • June 18, 2009 at 2:55 am

    #1011529

    At the end of your statement add 'COLLATE' followed by the collation name that you want the results in.

  • June 18, 2009 at 3:07 am

    #1011535

    Thanks for you reply I have tried this thing it works.....but I am looking for something much more smarter way to avoid it as, I have many queries of these types and we have many databases on diffrent locations

  • June 18, 2009 at 3:11 am

    #1011537

    I don't think there is a way other than converting the databases to be in the same collation, collation is a very annoying thing when you get into it for cross collation queries and for converting databases.

  • June 18, 2009 at 5:30 am

    #1011641

    you can ignore this when creating a linked server, there is a collation option , you can turn it off or on , if you are using 2005, you can change collation of a column, this will be more complex and annoying when you write SQL 😀

  • June 19, 2009 at 4:39 am

    #1012269

    You could just use COLLATE DATABASE_DEFAULT.

    At least you would not need to know what collation setting you are playing with. You would still need to put this into each of the queries that have the problem

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply