Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

collation of the value is unresolved due to a collation conflict

Posted on 2009-07-15
5
Medium Priority
?
654 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
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
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
Suggested Courses

885 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