Solved

collation of the value is unresolved due to a collation conflict

Posted on 2009-07-15
5
623 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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

943 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

10 Experts available now in Live!

Get 1:1 Help Now