how to keep two databases with different collation on one instence

  • hi,

    i want to keep two databses with diffrent collation on one instence of sqlserver but following is the problem i am getting.

    sqlserver is on  Thai_CI_AS and one of the database is on Thai_CI_AS and other one is on SQL_Latin1_General_CP1_CI_AS

    the one on SQL_Latin1_General_CP1_CI_AS is creating problem .

    select * into #tempt from xyz

    select * from #tempt t

    join bridge b on

    I found the tempdb is on Thai_CI_AS so this query was giving error

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS" in the equal to operation.

    how can i resolve this problem.

    is there any setting in master databse where i can tell that each database should use its own collation in tempdb rather than piking tempdb's collation.

    2) can put something in connection string which can override the tempdb's collation

    3) can put some setting in stored procedure which can override the tempdb's collation.


    your sincerely



  • join bridge b on COLLATE SQL_Latin1_General_CP1_CI_AS


    join bridge b on COLLATE Thai_CI_AS

    But you better explicitly create #tempt defining desired collations:

    CREATE TABLE #tempt (
    [name] varchar(?)   COLLATE SQL_Latin1_General_CP1_CI_AS

    Code for TallyGenerator

  • We do not want to change the script, as there are many stored procedures and functions.

    if anything is there at system databases, or server level setting for databases, that would be helpfull.

  • This was removed by the editor as SPAM

  • No your stuck with modifying the scripts I'm afraid.

    The only thing would be install a named instance on the same server with the right collation and move the inconsistent DB to that new instance instead.

  • note that it is better to use "COLLATE DATABASE_DEFAULT" than to hardcode a specific collation (unless there is a need to use a very specific collation) as that will allow you in future to change the collation of the database where your code resides without having to change the code itself.


Viewing 6 posts - 1 through 5 (of 5 total)

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