Problem with different collation from different databases
-
Hi..
I have a problem....
I need to attach a database that has different collation from the server collation.
The problem arises when a store proc runs because has a select union between a table, in the database with different collation, and another one in the master database (sysobject).
Do you know hot to attach database with right collation or some workaround...
thank for your useful contribuition.
-
I think you have two options.
Change the collation on the tables so that the collation is the same using ALTER TABLE. The easiest way here is to generate ALTER TABLE statements through a script that scans the system tables. I think you need to do each Column.
Specify the collation to use in the JOIN clause. Something like ...
INNER JOIN tblA a
ON a.Field1 COLLATE SQL_Latin1_General_Cp1250_CS_AS = b.Field2 SQL_Latin1_General_Cp1250_CS_AS
Hope this helps
Allen
-
Allen - I think he said 'UNION', but I suspect the same would apply IE
UNION
SELECT a.Field1 COLLATE SQL_Latin1_General_Cp1250_CS_AS
FROM a
etc
Collations have been one of my biggest headaches over the last few years (we have several DB developers, all at satellite locations, all of which seemed to end up with slightly different collation settings). The main problems tend to occur when restoring a backup on a different server with a different collation, everything seems OK until code using temp tables is used, which falls over as the temp tables end up with a different collation.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Oops - thanks Giles - I missed that.
Yep - different collations just cause pain. I try to have a standard base SQL build here but even so sometimes you need different collations.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply