collation of the value is unresolved due to a collation conflict

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
LVL 1
ZekeLAAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> 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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
 
ZekeLAAuthor Commented:
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
 
ZekeLAAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome and glad to help you out
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.