When our servers were built the regional settings were left as US even though we are in the UK.
SQL 2000 was then installed using the defaults and so the codepage is set to
Not realising that the locale being used in the company was US I built another server and set the default locale to UK and then installed SQL server 2000 which resulted in SQL having a different collation.
When I resotred some databases to my new server there were problems with some of the views and it was traced back to the collation settings.
Looking forward, should the company resolve the fact that we have an incorrect collation on our SQL servers? The implication of this is that all SQL servers will have to be built with US regional settings - otherwise our databases will not function.
Is there a compelling reason why we should resolve this - or just stick with it? What are the implications of sticking or changing?
The main reason which I had to address here was that every time your code-based object attempts to link a permanent table/view/function from a database with a different collation than the server default with a temporary table on a (n)char/(n)varchar field, - you have to use COLLATE <collation_name>. I ended up altering the database collation after changing collation of all tables in it. The issue originated from "someone's" improper upgrade procedures while not paying attention to things like this.