Solved

collation of the value is unresolved due to a collation conflict

Posted on 2009-07-15
5
644 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
[X]
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
  • 3
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24860152
0
 
LVL 1

Author Comment

by:ZekeLA
ID: 24863923
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
ID: 24865689
>> 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
ID: 31603765
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
ID: 24911488
Welcome and glad to help you out
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

726 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