New install select collation

Question

Post reply

New install select collation

RichB

SSCrazy Eights

Points: 9651
More actions
November 17, 2005 at 8:32 am

#111512

Hi,

Setting up a hotspare server. Need collation same as production.
This is Compatibility_52_409_30003.
Anyone have any idea what collation that equates to on the install options?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 1

Hi I played a little and this could be of some help to you:

select collationproperty ('Compatibility_52_409_30003','CodePage') 'CodePage'

select collationproperty ('Compatibility_52_409_30003','LCID') 'LCID'

select collationproperty ('Compatibility_52_409_30003','ComparisonStyle') 'ComparisonStyle'

by using collationproperty function I was able to determine 3 properties of your collation.

Next, I used fn_helpcollations function with the properties in where clause:

SELECT name, collationproperty (name,'CodePage'), collationproperty (name,'LCID'), collationproperty (name,'ComparisonStyle')

FROM ::fn_helpcollations()

where collationproperty (name,'CodePage')=1252

and collationproperty (name,'LCID')=1033

and collationproperty (name,'ComparisonStyle')=196611

this returns two collations

Latin1_General_CI_AI

SQL_Latin1_General_CP1_CI_AI

I believe these are equivalent, the first one you choose in Collation Designator section and the second one under SQL compatibility during the installation. But this is just my opinion, try to confirm from other sources as well.

RichB SSCrazy Eights Points: 9651 More actions · Answer 2

Thanks, I've already got that far -

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

All these selects give the error:

Server: Msg 446, Level 16, State 9, Line 1

(collations incompatible)

sadly it doesn't work

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

It does work ... you need to make the COLLATION between any 2 columns used in any relational operation th same. Below is you example modified a bit (it now works):

drop table testcollate2

go

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 collate SQL_Latin1_General_CP1_CI_AI

select * from testcollate2 where a = c collate SQL_Latin1_General_CP1_CI_AI

select * from testcollate2 where b collate SQL_Latin1_General_CP1_CI_AI = c collate SQL_Latin1_General_CP1_CI_AI

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

yes, thanks.

The problem is the live server (900+ procs) is created with the default collation in this compatability nonsense. The live db is also in this collation.

Now, if I have to restore this database to a new server, I need the server to be in this collation too in order to guarentee that the temp tables and variables will work properly without throwing collation conflicts.

Sadly I cannot find the install option to use this collation. So, the question is how do I get the second server configured with this collation.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

This might be worth investing in the time (and money) for a call to MS PSS.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RichB SSCrazy Eights Points: 9651 More actions · Answer 6

mmm.

At the moment I am pursuing the unattended install path... will let you know if it works!

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

Yup, unattended install with a customised setup.iss worked a treat.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

AM I missing something ? An attended installtion does not offer the collation that you need but an unattended installation does ? If this is the case is there any doc on this ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RichB SSCrazy Eights Points: 9651 More actions · Answer 9

Thats pretty much it, yup.

The menus on the install don't seem to offer the collation, nor does the fn_collations or whateer it is proc.

But the collation exists on the database server, you can specify it in sql - eg columns, databases etc. Just doesn't show in the install options.

So by taking an unattended install - or editing setup.iss and installing from the command prompt using it, one can textually specify the collation.

Found no usefull documentation on this anywhere - so either I am being incredibly stupid, or just have a special system! Anyway, figured I'd share the results with the community.