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.
You can change the system collation by rebuilding the master DB.
This is done on SQL 2000 using the program Rebuildm.exe
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.
answered Nov 21, 2010 at 11:35 PM
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.
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".
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!.
answered Nov 22, 2010 at 10:10 AM