Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Aspenbat
Starting Member
4 Posts |
 Posted - 2007-11-07 : 10:56:09
|
| I have upgraded many of my servers from 2000,SP2 to 2005. Whilst doing 1 server I have the following error message:Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation..To continue, correct the problem, and then run SQL Server Setup again. I have tried to search in many places for this error whilst upgrading but found none, please help. |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 11:05:17
|
| the database collation and column collation are likely different. if this is a test enviroment you can check the database and change it to other one mentioned in the error message.ALTER DATABASE report2 COLLATE SQL_Latin1_General_CP1_CI_ASyou may have problems if the database has functions already created and dependant on that database collation. ultimately you need to decide what collation you want for the database and the tables. if they will not be the same you will need to add COLLATE statements to prevent the error you are receiveing, if they will be the same then it is a matter of getting everything converted over to the correct one. please post your progress |
 |
|
|
Aspenbat
Starting Member
4 Posts |
Posted - 2007-11-07 : 11:11:16
|
| As the error message comes up whilst I am carrying out the upgrade it does not tell me which database has the error. I have 138 databases on this server ! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 12:21:40
|
| If you have a test environment you could try installing "plain" SQL 2005, and Attaching each database and seeing if you get any complaint during the Attach"I have 138 databases on this server !"That isn't going to make this approach popular!Kristen |
|
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 12:53:52
|
| without knowing the query or even database the query is in, it will be difficult to find the collation difference. can you restore the databases to another sql 2000 server then run drop/create scripts for each to rule out the possibility of this problem pre-existing the upgrade? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 00:49:00
|
| Does the Upgrade Advisor say anything? |
|
|
|
Aspenbat
Starting Member
4 Posts |
Posted - 2007-11-08 : 05:17:41
|
| Thank you all for your help. Kirsten - I am not familiar with the Upgrade Advisor, please expand.When it fails upgrade, I can go back to the log and have found this SQL Server Setup has encountered the following problem: Failed to execute the Transact-SQL statement: CREATE PROCEDURE sp_sqlagent_get_perf_counters@all_counters BIT = 0ASBEGINSET NOCO...Script file: sqlagent90_msdb_upgrade.sqlThe error was: [Microsoft][SQL Native Client][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.. To continue, correct the problem, and then run SQL Server Setup again.Now am I to believe the problem is in the msdb database as that is the script which runs and then falls over ? If so then I cannot change the Collation of a system database so does this mean I have to detach all databases and rebuild the master ? or does soemone have a special magical method to overcome this. |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Collation Error on s2000 Upgrade