how to keep two databases with different collation on one instence

Question

how to keep two databases with different collation on one instence

rajemessage 14195

Hall of Fame

Points: 3299
More actions
December 9, 2022 at 4:15 am

#4123646

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 t.name=b.name.
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

December 9, 2022 at 7:10 am

This was removed by the editor as SPAM

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

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

join bridge b on t.name COLLATE SQL_Latin1_General_CP1_CI_AS =b.name

OR

join bridge b on t.name =b.name 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

rajemessage 14195 Hall of Fame Points: 3299 More actions · Answer 2

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.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

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.

frederico_fonseca SSCoach Points: 16017 More actions · Answer 4

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.