Solved

collation of the value is unresolved due to a collation conflict

Posted on 2009-07-15
5
617 Views
Last Modified: 2013-12-05
I recently moved my sql 2000 databases from one laptop to another and I'm now getting a collation problem running a query against my ZLATimeTrack database.

My collation values are as follows:
  SELECT SERVERPROPERTY('Collation')  -- Latin1_General_CI_AS
  SELECT DATABASEPROPERTYEX('tempdb', 'Collation')  -- Latin1_General_CI_AS
  SELECT DATABASEPROPERTYEX('ZLATimeTrack', 'Collation')  -- SQL_Latin1_General_CP1_CI_AS

I would prefer to have the database defaults be SQL_Latin1_General_CP1_CI_AS but as a temporary fix I tried executing the following:
  ALTER DATABASE ZLATimeTrack COLLATE Latin1_General_CI_AS

which produced the following errors:
  Server: Msg 5075, Level 16, State 1, Line 7
  The column 'TimeEntries.Hours' is dependent on database collation.
  Server: Msg 5075, Level 16, State 1, Line 7
  The column 'uSplit.Value' is dependent on database collation.
  Server: Msg 5072, Level 16, State 1, Line 7
  ALTER DATABASE failed. The default collation of database 'ZLATimeTrack' cannot be set to Latin1_General_CI_AS.

TimeEntries.Hours is a computed column.

I'm thinking of just using the Rebuild Master utility but there are warnings that the database objects will be dropped. Do the warnings only apply to objects in the master database or in my other databases on the server?

Here's one link I found on rebuilding the master: http://msdn.microsoft.com/en-us/library/aa197950(SQL.80).aspx
0
Comment
Question by:ZekeLA
  • 3
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
0
 
LVL 1

Author Comment

by:ZekeLA
Comment Utility
That doesn't seem to apply to my situation. There are no check constraints in my database. Moreover, I don't really want to modify every sql statement in my database as a work-around the problem.

I still need to know if
1) rebuild master only affects the master database or all user databases.
2) what I need to do to let ALTER DATABASE ZLATimeTrack COLLATE Latin1_General_CI_AS
execute succesfully.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
>> 1) rebuild master only affects the master database or all user databases.

Rebuild master database resets the system level configuration and make sure that your Maintenance plans are backed up and users are scripted so that you wont miss it out. It can help you in resetting your Master database collation and not your user database collations.

>> 2) what I need to do to let ALTER DATABASE ZLATimeTrack COLLATE Latin1_General_CI_AS execute succesfully.

Nail down where it exactly causes the issue and correct that one to get it done.
In our scenario, we have two errors

  The column 'TimeEntries.Hours' is dependent on database collation.
  The column 'uSplit.Value' is dependent on database collation.

Since these columns are dependent on database collation, I would recommend you to alter collation of these columns to Latin1_General_CI_AS first and then try ALTER DATABASE again which would help you.
0
 
LVL 1

Author Closing Comment

by:ZekeLA
Comment Utility
I rebuilt the master database. Even though it only affects the master, I did have to reattach all of my user databases and add back some logins. Not a big deal. It's not surprising in hindsight. Thank you for your help.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Welcome and glad to help you out
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now