Changing Collation for SQL Server 2008 R2 Express
Written by Ian McIntosh Friday, 1 July 2011
This article builds on the previous post and is part of a series to setup local and remote working environments for MojoPortal.
If you have differing collations on your local and remote databases, you are likely at some stage to see collation errors.
The way I have chosen to address this is to change the collation of my local server.
This is what worked for me on my specific setup. I can’t make any guarantees that this will work for you. If you try it, make sure you’re well backed up first.
What I did
- I’m running windows 7 starter on a Samsung N210 netbook. First, I downloaded and installed Windows 7 Service Pack 1 via Windows Updates
- Crucially, I installed SQL Server 2008 R2 Express Service Pack 1 from here. If you don’t have service pack 1 installed, it probably won’t work (there is a bug)
- I backed up my databases
- I detached my databases (but I think that’s unnecessary as it gets done automatically by rebuilddatabase)
- I opened up a CMD prompt as admin (type cmd then press ctrl, shift and enter)
- I navigated to c:\program files\microsoft sql server\100\setup bootstrap\sqlserver2008r2 (which has setup.exe)
- I typed:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=sqlexpress /SQLSYSADMINACCOUNTS=<domain>\<user> /sapwd=**** /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
- This took a few minutes to run, but at least it was successful!
- I could then check my tempdb and see its collation had changed
Once you have changed the collation of your local database, it’s best to discard your local database and recreate it as this will then have consistent collation.
Will this completely change the collation for my existing databases?
No. At least, it didn’t for me. Collation can also be set at column level. In my case at least, the collation appears to be set individually for most columns of every table, for each database. Apart from manually going in and changing each setting – which I don’t want to do – I haven’t got a way forward yet, but I am working on it!
So what use is this post?
The collation is now changed for the server, so any new databases you create will have the new collation. As long as you can start again, your next database should be in the new collation.
This is the short, to the point version of my more rambling post on this subject.
Looking for quality mojoPortal Hosting? Look no further than Arvixe Web Hosting.