Cannot resolve the collation conflict

I am running MS SQL 2005 with several development databases. Somehow the collation between tempdb and one of my db's got messed up. Now I'm getting this error all over the place:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I tried changing tempdb's collation but ms sql wouldn't alllow it on a system db.  There's too many tables involved to slap collate statements everywhere. Short of dropping the data, is there anyway to get the 2 back in synch? This problem is driving me nuts..

LVL 53
_agx_Asked:
Who is Participating?
 
_agx_Connect With a Mentor Author Commented:
For anyone else that needs to reinstall and change the collation, you need to:
1) Uncheck the "Hide advanced configuration options" checkbox during installation AND
2) A few screens in, select the collation "Dictionary order, case-insensitive, for use with 1252 Character set"
http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic21538.aspx

0
 
rajvjaCommented:
Hi
When do you get this error? I mean during select or any other...
0
 
rajvjaCommented:
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
_agx_Author Commented:
Usually select, but like I said it's a global problem. So I need to fix it on a db level.
0
 
geek_vjCommented:
If you want to change the user db's collation to match the system db's collation, then it is a simple operation. You just have to execute the below query:

alter database <dbname> collate 'Systemdb's collation here'

If you want to change system db's collation, then you need to take backup of all dbs, logins and jobs and rebuild master database so that they match the user db collation. Here is the sample command you need to execute from command prompt:
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="password"
/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=<Targetcollation name which is your user db collation>
0
 
_agx_Author Commented:
if you want to change the user db's collation to match the system db's collation

@geek_vj - But that would only affect new tables right? ie The problem would still occur for existing tables I think?

setup.exe /q

Where's the .exe located?
0
 
_agx_Author Commented:
@rajvja - Thanks, but as I mentioned ms sql won't let me alter a system db's collation.
0
 
_agx_Author Commented:
Anyone, Bueller?
0
 
_agx_Author Commented:
I tried setup.exe  /ACTION=RebuildDatabase .... but it failed w/a fatal error.

Anyone else have any thoughts?
0
 
geek_vjCommented:
Please confirm if you are trying to change the system dbs collation or user database?
0
 
geek_vjCommented:
>> Where's the .exe located?

setup.exe is located in the same as the setup file. Usually it will be in dvd drive.
0
 
_agx_Author Commented:
Sorry, can you be more specific? Originally I installed from a download, not a dvd.
0
 
_agx_Author Commented:
Please confirm if you are trying to change the system dbs collation or user database?

I assume your instructions were for changing the system db.  Which is ok. I eventually found the setup file and tried your instructions, but it just crashed.  
0
 
geek_vjCommented:
If you have done it from a download location, then you need to open the command prompt, navigate to the same path and execute the command provided above. However, ensure that all the backup of user databases, logins and jobs were taken prior to executing the command.

For example, if you have the download in "G:\SQLServer\" location, then you need to go to command prompt, navigate to G:\SQLServer\ and then execute the above command. (Please ensure that the download location should be same as the first time you have installed sql server)
0
 
geek_vjCommented:
>> I assume your instructions were for changing the system db.
Yes, it is.
0
 
_agx_Author Commented:
Tried that, but it still crashes with a fatal error.

Looks like I'm just going to have to reinstall..
0
 
geek_vjCommented:
Please post the exact error you are getting.
0
 
_agx_Author Commented:
Oops, too late. I've already begun uninstalling. But it was mostly hex codes with a "report this error" to microsoft ...
0
 
geek_vjCommented:
Ohh...you should be able to get the error info from the following location:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

Please send the error message if any. Also, if you have any other queries, please post the same.
0
 
_agx_Author Commented:
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="password"
/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS"


Btw: I think those may be the instructions for 2008.  The ones for 2005 look a little different.  Might be why it crashed.
0
 
_agx_Author Commented:
Thanks for trying to help everyone. But I didn't have any luck with the suggestions. So I'm just going with a reinstall.
0
 
geek_vjCommented:
There is a little difference in the options for SQL 2005 for server level collation change. Thanks for pointing it out.
0
 
_agx_Author Commented:
Appreciate the help. But in the end there was no avoiding a reinstall.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.