New install Collation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

New install Collation problem

Author

Topic

Wang
Starting Member

48 Posts

Posted - 2005-11-17 : 10:25:48

Hi
I am trying to set up a hotspare server. This needs the same collation as production, being Compatibility_52_409_30003.

I am having difficulty finding how to install with this collation, not helped by ::fn_helpcollations() not returning this on the server that IS set up this way. :s

If anyone can point me in the right direction I would be very happy indeed...

Cheers.

Wang
Starting Member

48 Posts

Posted - 2005-11-17 : 13:05:18

Ok I've done some research and I think this is supposed to equate to latin1 us english 1252 case insesitive accent insensitive karna insensitive width insensitive sort order52.

I just can't find a collation that seems compatible with this.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 13:20:39

Change the Collation as appropriate; this will tell you its CodePage, LCID and ComparisonStyle

SELECT 'CodePage', 'SQL_Latin1_General_CP1_CI_AS', COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage')
UNION
SELECT 'LCID', 'SQL_Latin1_General_CP1_CI_AS', COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID')
UNION
SELECT 'ComparisonStyle', 'SQL_Latin1_General_CP1_CI_AS', COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle')

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-17 : 13:54:52

Thanks that was a usefull bit of information. Now its late in the day and I think I'm feeling stupid...

select name,
COLLATIONPROPERTY(name, 'LCID'),
COLLATIONPROPERTY(name, 'CodePage'),
COLLATIONPROPERTY(name, 'ComparisonStyle')
from ::fn_helpcollations()
where COLLATIONPROPERTY(name, 'LCID') = COLLATIONPROPERTY('Compatibility_52_409_30003', 'LCID')
and COLLATIONPROPERTY(name, 'CodePage') = COLLATIONPROPERTY('Compatibility_52_409_30003', 'CodePage')
and COLLATIONPROPERTY(name, 'ComparisonStyle') =COLLATIONPROPERTY('Compatibility_52_409_30003', 'ComparisonStyle')

create table testcollate2
(
a varchar(250) collate Compatibility_52_409_30003 not null ,
b varchar(250) collate SQL_Latin1_General_CP1_CI_AI not null ,
c varchar(250) collate Latin1_General_CI_AI not null
)

insert into testcollate2(a, b, c)
select 'hi', 'hi', 'hi'

select * from testcollate2 where a = b
select * from testcollate2 where a = c
select * from testcollate2 where b = c

Gives the error:
Server: Msg 446, Level 16, State 9, Line 1

How can I set up this server to use this collation that doesn't exist on the setup menu - indeed how can I even specify it when it doesn't exist in fn_helpcollations?

Feeling rather stumped, would really appreciate some guidance!

Cannot resolve collation conflict for equal to operation.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:00:48

"select * from testcollate2 where a = b"

You can't do that, columns [a] and [b] are different collations - so you will have to CAST them to make the comparison.

(The rest works fine, but because its a single statement the parser chucks it out on this specific point)

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-17 : 14:08:01

Yes, thats rather my point.

To recap:
Production server is Compatibility_52_409_30003 collation.
Currently the hotspare is Latin1_CI_AI.
If we had to live that, then there would be allsorts of issues with tempdb etc - temp tables and so on probably not working.

Eg:
-- Server collation = Latin1_General_CI_AI

create database testcollate collate Compatibility_52_409_30003

use testcollate
create table testcollate2
(
a varchar(250)
)

create table #tc(a varchar(250))
select * from #tc tc join testcollate2 t on t.a =tc.a

IE: hotspare will be unliekly to work.
I cannot figure out how to set up the server with Compatibility_52_409_30003 default collation (eg for tempdb etc).

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:16:32

Well, we explicitly specify the collation whenever it is needed, viz:

Every varchar/char/text in CREATE TABLE
(Including ALL @VarTable and #TempTable; SELECT * INTO #TempTable FROM MyTable is therefore not possible)

All comparison tests which involve external databases - e.g.


SELECT *
FROM MyTable AS T
     JOIN sysobjects AS O
        ON T.MyName = O.name COLLATE SQL_Latin1_General_CP1_CI_AS

and then we don't have to worry when we roll out on a server with a different collation (or at least, the collations that we have encountered so far!)

We have our QA server set to a different collation to flush out code where we "forget" !

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-17 : 14:33:17

heh - nice solution, I would happily go down that route.
Trouble is I need to build this asap, without messing about with existing code.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:44:53

That wouldn't wash here ... spend an hour today, save 10 hours tomorrow ...

Actually it won't take you an hour to change the COLLATION on the QA database to Chinese, Case Sensitive, right-to-left and bottom-to-top alignment!

Mind you, there is always the risk of some "line the DBAs up against the wall and shoot them" when the testers get to see what's happened!

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-18 : 05:37:34

Changing the collation on the production database would involve pumping out 150gig of data, scripting and updating 700+ tables reloading the data, checking by hand itro 900+ procs and gok how much inline code, taking the server down for however long to rebuild master to a sensible collation adn then hoping it all works.

Afaict its not an option at this point, and I simply need to find out how to get this collation installed as server collation on the other servers.

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 10:08:37

No need to change that data, or the databases, just need to be sure that all #Temp and @Var tables have COLLATE on the CREATE TABLE statements for char/varchar/text and there are no SELECT * INTO #TempTable FROM ... statements (bad form anyway, very inefficient). The problem is that without the COLLATE statement they will get created with default collation - and if that's different then all comparison operators will raise error.

Decent text editor will do a global find across all the source code for them pretty easily!

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-18 : 13:08:20

yeah, would be nice, but if it doesn't work cleanly then I've just taken out maybe 1m euros worth of turnover per day... funnily enough its the ones that select into that DO work atm. Crazy.
Personally I would love to just rewrite the lot, but that too is not an option right now...

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 19:51:27

"select into"

Well ... if they
SELECT INTO MyPermenantTable
they will get the default collation for the target (current, probably) database

If they
SELECT INTO #MyTempTable
they will get the collation for TEMPDB

Dunno if that explains it?

So you now only have to fix the
SELECT INTO #MyTempTable
so probably only 0.5m euros lost ... but the automated testing suite will find those issues before they ever get to production, right?!

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-21 : 09:22:13

Ha!

A comedian in our midst...

Kristen
Test

22859 Posts

Posted - 2005-11-21 : 13:26:24

On the contrary, an opportunist. I now have an extra name to add to my spam list for our automated testing system!

Kristen

Wang
Starting Member

48 Posts

Posted - 2005-11-21 : 15:22:01

:o

Shocking!

:p

That is just one of those things I would love to have. Sadly being the new boy, I have a large number of other priorities - like getting a hotspare server sorted.

I am currently pursuing the path of the unattended install, where it _looks_ like I can specify the collation. Will let you know if it works...

Kristen
Test

22859 Posts

Posted - 2005-11-22 : 01:55:38

"Shocking!"

Wang
Starting Member

48 Posts

Posted - 2005-11-22 : 06:45:22

heh

Unattended install with a customised setup.iss worked a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources