Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Cannot resolve the collation conflict

Posted on 2011-02-11
25
606 Views
Last Modified: 2012-05-11
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..

0
Comment
Question by:_agx_
  • 13
  • 8
  • 4
25 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 34868890
Hi
When do you get this error? I mean during select or any other...
0
 
LVL 11

Expert Comment

by:rajvja
ID: 34868903
0
 
LVL 52

Author Comment

by:_agx_
ID: 34868908
Usually select, but like I said it's a global problem. So I need to fix it on a db level.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:geek_vj
ID: 34868909
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
 
LVL 11

Expert Comment

by:rajvja
ID: 34868919
0
 
LVL 52

Author Comment

by:_agx_
ID: 34868925
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
 
LVL 52

Author Comment

by:_agx_
ID: 34868928
@rajvja - Thanks, but as I mentioned ms sql won't let me alter a system db's collation.
0
 
LVL 11

Expert Comment

by:rajvja
ID: 34868951
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869041
Anyone, Bueller?
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869302
I tried setup.exe  /ACTION=RebuildDatabase .... but it failed w/a fatal error.

Anyone else have any thoughts?
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869311
Please confirm if you are trying to change the system dbs collation or user database?
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869344
>> Where's the .exe located?

setup.exe is located in the same as the setup file. Usually it will be in dvd drive.
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869472
Sorry, can you be more specific? Originally I installed from a download, not a dvd.
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869513
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869572
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869581
>> I assume your instructions were for changing the system db.
Yes, it is.
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869596
Tried that, but it still crashes with a fatal error.

Looks like I'm just going to have to reinstall..
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869628
Please post the exact error you are getting.
0
 
LVL 52

Author Comment

by:_agx_
ID: 34869673
Oops, too late. I've already begun uninstalling. But it was mostly hex codes with a "report this error" to microsoft ...
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869703
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
 
LVL 52

Author Comment

by:_agx_
ID: 34869707
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
 
LVL 52

Author Comment

by:_agx_
ID: 34869721
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 34869743
There is a little difference in the options for SQL 2005 for server level collation change. Thanks for pointing it out.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 0 total points
ID: 34869955
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
 
LVL 52

Author Closing Comment

by:_agx_
ID: 34904643
Appreciate the help. But in the end there was no avoiding a reinstall.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question