[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.9

Change Collation Failure!  Please Help.

Asked by poogy21 in SQL Server 2005, MS SQL Server

Tags: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Hello, Experts.

I'm migrating a database from one server to the other using the 'restore db from backup' method.  I first created an empty database, then restored the backup file over.

Now I've run into a conflict of collation between the new host server default collation and my database.  


I've tried all of the following:
Alter the collation on the database / catalog, by using:
ALTER DATABASE mydata COLLATE ... etc.  

Error: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'mydata' cannot be set to SQL_Latin1_General_CP1_CI_AS.


Secondly, I tried to alter the master database collation by rebuilding the database like so:
start /wait C:\SQLTOOLS\setup.exe INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=pass SQLCOLLATION=Latin1_General_CI_AI

This processed failed with the following error:
The installer has encountered an unexpected error. the error code is 2711. The specified feature name ('SQL_Engine') not found in Feature table.

I tried to delete tempdb, re-start SQL Service, etc.  No change yet.


The database is up and working.. it's just that some procs are failing due to collation issues.

Lastly, I should add that I'm running version:
"Microsoft SQL Server 2005 - 9.00.1399.06 (X64)   Oct 14 2005 00:35:21   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) "


Thank you all in advance for all your help!

- Eyal

[+][-]08/12/08 07:38 PM, ID: 22218686Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: SQL Server 2005, MS SQL Server
Tags: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Sign Up Now!
Solution Provided By: chapmandew
Participating Experts: 2
Solution Grade: B
 
[+][-]08/12/08 09:01 PM, ID: 22218903Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]08/19/08 10:39 AM, ID: 22261997Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-92 - Hierarchy / EE_QW_2_20070628