I have seen collation conflict errors on my websites on occasion. Although I have never specified anything to do with collation, it seems the defaults for my local installation and that on my hosting server are different. Its not just me, there are plenty of pages on the internet about it.
Initially, I thought I just needed to change the collation of one database to match the other. I don't have sufficient access rights to do that for my hosting server, but I do for my local database.
However, it turns out that's not the whole story. There's also a database called tempdb that also comes into it. And this complicates matters. This is a system database, and because its a system database, I cannot change the collation of this (thanks Microsoft). Collation is set during installation and for the system databases, that's that.
Well, almost. I then discovered you can change the collation, but only by rebuilding the master database. This sounds pretty nasty, and is certainly something where you want to make sure you're well backed up before you start.
Again, there's quite a bit on the internet about it. Unfortunately, quite a lot of the available information is also wrong (or incomplete). Bummer.
The basic information is available here. When I tried it, it didn't work. I didn't really get any useful information why, just a lot of logs that didn't really say anything useful. I found a work-around, which was to run setup from the DVD - but I downloaded it so I have no DVD.
I searched for a service pack, but was not sure if there was a service specific to r2. Eventually I found it, theres a link to it below.
I'm not sure the info I found was for express. It said to use mssqlserver as the instance name if you didn't specify one. I used sqlexpress. It also said to run from \release folder. I ran from \sqlserver2008r2 folder. I'm not sure which of these issues is important, I just know I eventually got it to work after trying numerous things over many hours.
After eventually getting it to change collation successfully, I went on to attach my database back in. That now failed. I did read some stuff about exporting using some tool or other then importing back in, but I didn't do it. I had also read I could just detach then reattach later. And I have a back up, so what could go wrong eh?
Well, fingers crossed for me now as I'm about to attempt to get my database back now. I've renamed the database file in windows explorer so I can create a new one of the same name. Then I'm going to try downloading my published database. I'll update this with the results.
For this to stand a fighting chance, I'm going to need to add back my database user name, as used in my connection string. To do this, I first need to create a login, by going into security | logins, right click, select new login. Create it as an sql server authentication login. Then I can go into my database | security | users, right-click & select new user. For my purposes I select it as a dbowner.
I have now successfully downloaded my published site (I did need to make a change to my newly created login to stop it requesting a change of password), and re-uploaded it. That was all successful & now all databases on both sides have a consistent collation.
One thing in all this that I hadn't even considered - what if getting consistent collation doesn't fix my collation conflict. All the things I've read suggested it should. But it doesn't. Oh hell, looks like I'll be loosing a bit more hair on this one.
But, at least I found a way to change collation. Just in case its of use to anyone, here's a summary.
What worked for me:
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.
I backed up my databases
I detached my databases (but I think thats unnecessary as it gets done automatically by rebuilddatabase)
I opened up a cmd prompt as admin (type cmd then press ctrl, shift & 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 was successful!
I could then check my tempdb and see its collation had changed.