x

How to change server collation SQL 2000

Hi everybody!

I have a big problem right know, I need to change the collation Latin1_General_CI_AS, to Latin1_General_CP1_CI_AS. How can I do it? I need to change it in the server, not databases. All the edatabases I restored, are with Latin1_General_CP1_CI_AS. Thanks a lot for your help! Regards.
more ▼

asked Nov 21, 2010 at 03:49 PM in Default

Maripili gravatar image

Maripili
394 32 33 35

I don't believe that you have to do anything because CP1 is a default, so if it is not spelled out in the collation name then CP1 is assumed anyway.
Nov 21, 2010 at 07:37 PM Oleg
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can change the system collation by rebuilding the master DB.

This is done on SQL 2000 using the program [Rebuildm.exe][1]

Please be aware that doing this will also delete associations to user databases (you can re-attach them afterwards). Your security information may need working on too (I am not too sure, but think that the system logins will be deleted too).

This should save the hassle of re-installing SQL Server completely.

[1]: http://msdn.microsoft.com/en-us/library/aa197950(SQL.80).aspx
more ▼

answered Nov 21, 2010 at 11:35 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@WilliamD I am really curious, aren't these 2 already the same? I don't have the link handy, but can swear that CP1 is assumed if ommitted. This seems to make Latin1_General_CI_AS and Latin1_General_CP1_CI_AS. Both are case insensitive, accent sensitive with default out of the box encoding.
Nov 22, 2010 at 07:50 AM Oleg

@Oleg - That is a good question. CP1 means Codepage 1252 whereas all other CP??? refer to specific codepages 'properly' (http://msdn.microsoft.com/en-us/library/ms180175.aspx). I am not so hot on Codepages, but maybe you know the relevance here. I think codepage 1252 is the standard windows codepage, and again, something that MS has made as it's own standard.

I am not sure if the two collations you state are then the same if the host OS for SQL server is set to a different codepage (is that possible?).

Either way, there is no need to reinstall sql server to fix this sort of thing.

If you find anything out to suggest they are always the same, or not for that fact, please do let me/us know.
Nov 22, 2010 at 08:04 AM WilliamD

@WilliamD Tried, but could not find anything. I know what codepage does, it simply maps the order of characters, and if any character does not fit then it is replaced with something like question mark. This allows the engine to figure out that aZZZZZZZ is still smaller than b when compared. The look of the letters is irrelevant. What I mean is that if I consider English latter a and Russian latter a, they are different to the server though they look identically on the screen, and sound identically in some cases.

declare @en char(1);
declare @ru char(1);

select @en = 'a', @ru = 'а';

select 
    @en en, @ru ru, 
    cast(@en as binary(1)) code_en, 
    cast(@ru as binary(1)) code_ru;

-- produces
en   ru   code_en code_ru
---- ---- ------- -------
a    ?    0x61    0x3F
Coming back to the question, it would be nice to find a confirmation that if the code page is not specified then the one from the regional settings will be used, and out of the box, this should match CP1252 a.k.a. CP1
Nov 22, 2010 at 08:50 AM Oleg
(comments are locked)
10|1200 characters needed characters left

Hi everybody!

Yesterday I work all the night doing this and I have the answere. I was searching and googling.. :-) And all of you have some part of the answeres. I want to say you how I fixed it, and may be help someone else.

Schema: The server were changed on Friday night. Saturday work the people only half-day. The server traveled to another location (city) one day before and there. Yesterday were nobody to help me, it was alredy night and nobody have to know about my mistake. Also, the installer cd's were alredy with me, and I need to fixed it before monday morning, when all the people begin with the work.

Solution:

First, I installed the SQL Server with the collation Latin1_General_CI_AS and didn't see that it should be this one: Latin1_General_CP1_CI_AS. They are not the same, because on saturday after we bring on production the collection problems came out with the software the users use. So I had really to fix it.

First, to change the server collation, I had to do it only with rebuild.exe. (There's no other way) I backed up all the databases, and detach them after that (so if it don't delete it would be more easy to attach them again)

I backed up the msdb also, to restore it, and don't have to re-configurate the jobs and the dts's. I had all the logins alredy in my local computer. I copied them with the Idera tool (like here you teached me ;-))

So, during this procedure I copied to this server the files required from the cd to rebuild de master. (x86/Data).

When I was ready to do the rebuild procedure, I began and got the error "Rebuild master failed with error -1".
So i was in PANIC... really... so I was googling, and all the answere to this trouble was permissions. They said that when this happen, we should copy the files in the hard disk (thinking that you do this operation with the installation cd -> not my case) and take off the "Read-only" check from the folder and files. I did it, and again panic... it didn't resolve it. So I was really confused, then I though and did a lot of changes in the start of the sql server agent, maybe with the local admin user registered it could work. But it didn't so.

After that I only have the last test to do: trying to do the same operation but with the admin local user (I was logged as domain admin user). I installed all the SQL with the local admin user, after all was installed and configured I did the change of server workgroup, and put it in the domain. So, after that i did the same and the rebuild.exe was sucessfully installed!!!! I was so happy, I wanted to cry! :-D

Well, the other thing that took me time to find, was knowing where I should configure this collation during the installation (Latin1_General_CP1_CI_AS). I found it also. It's so, in the collation configuration window you should select the second option:

---> 'SQL Collations' And there, in the big list you should select this option: -------->'Dictionary, case-insensitive, for use with 1252 character set'

After that all was fine, I log of as local admin user, and sing up as domain admin user. Then I copyed all the logins, after that I restored the msdb database, and all the other databases. I prove all my Jobs, and all where fine. The job which I knew that it were a collation error, run ok, and theere where no collation error anymore.

I reconfigure all my linked servers, and all backup stuff...

Finally, I slept well for four hours, then ready to go back to work!. :-)

I hope this can help someone!

Thank you very much for your help and advices!.

Regards,

Maripili
more ▼

answered Nov 22, 2010 at 10:10 AM

Maripili gravatar image

Maripili
394 32 33 35

(comments are locked)
10|1200 characters needed characters left
You need to uninstall and reinstall SQL Server 2000 with the proper collation.
more ▼

answered Nov 21, 2010 at 07:30 PM

ozamora gravatar image

ozamora
1.4k 2 3 5

I would say that you need to uninstall SQL server 2000 and install SQL Server 2008 R2. :D Not because of the collation issues but for the mainstream support from Microsoft.
Nov 22, 2010 at 06:48 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x22

asked: Nov 21, 2010 at 03:49 PM

Seen: 4963 times

Last Updated: Nov 21, 2010 at 11:47 PM