SQL Server 2000 sp3a install collation query

Question

SQL Server 2000 sp3a install collation query

dharper3

SSCrazy

Points: 2391
More actions
March 11, 2005 at 2:09 am

#163507

Hi, hope someone can assist. I have recently noticed that install of sql server 2000 with or without sp3a indicates a diferent collation depending whether the install was done on a Win 2000 or Win 2003 O/S. I have checked the obvious (locale, date, time keyboard settings) and these are all the same. Any idea why the collation when sql is installed on Win 2000 is
SQL _LATIN1_GENERAL_CP1_CI_AS and on Win 2003 it is
LATIN1_GENERAL_CI_AS
This is causing problems with sorts when migrating databases from one to the other. Build is STD edition not changing any defaults during install.
Thanks in advance
Derek
March 14, 2005 at 8:00 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

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 1

I saw this happen once--a new box was built, the collation order was off just as you said, and when the old databases were moved to the new box a load of tempdb work no longer functioned properly.

Sadly, I was on vacation that week , and so others got to deal with it. We poked around some, but from all that we could tell the default collation setting was the same for both W2k and W2k3. However, the fact that someone else has seen it makes me suspicious all over again. There might be some weirdzo setting out there that messes things up now and then.

Moral: Be certain to check the collation order upon installation, make it the Microsoft default if at all possible, and you'll never have to worry about it thereafter.

Philip

dharper3 SSCrazy Points: 2391 More actions · Answer 2

Thanks, but this is not adhoc as it occurs every time. Collation is different after install on win2k vs win2003, different servers etc. I agree now that its a smart move to check after install and alter but still none the wiser if this is expected behaviour and what causes it.

Derek

noeld SSC Guru Points: 96590 More actions · Answer 3

First One comment. Collation is a paramerer in the setup that must always be a conscious decision.

When you have a server with US locale SQL Setup does not bother to use Windows Collation, but it will always try to use Windows Collation otherwise. It is odd but it is like that!

There are some differences between the two an if you want to know about them go here.

BTW Windows Collation is the recommended path and SQL Collations are viewed as a backwards compatibility feature.

HTH

* Noel

dharper3 SSCrazy Points: 2391 More actions · Answer 4

Thanks will check the link. I agree it should be a concious decision but to date we have used vanilla defaults on install, both Win2000 and Win2003 have exact same locale etc yet the outcome is different, hence migrations of databases is made more complicated. If it is std behaviour that SQL collation will vary across the O/S's mentioned after vanilla install of SQL Server 2000 then ok guess I can live with that fact now I know it. But I was really wondering if no doco's / MS articles exist identifying this difference maybe just maybe I have missed an O/S setting that is affecting things??