SQL Server 2005: Collation Conflict Error when selecting Database Properties

[Peter Nurse]

I have just upgraded to SQL Server 2005 from SQL Server 2000.

In Microsoft SQL Server Management Studio, when I click on database
properties, I receive the following error:-

Cannot resolve the collation conflict between
"Latin1_General _CI_AS"
and "SQL_Latin1_Gen eral_CP1_CI_AS" in the equal to operation.
(Microsoft SQL Server, Error: 468)

Some reference suggest that I can change the database collation by
clicking database properties!

What can I do?

[Erland Sommarskog]

Peter Nurse (Pt****@yahoo.c om.au) writes:

I have just upgraded to SQL Server 2005 from SQL Server 2000.

In Microsoft SQL Server Management Studio, when I click on database
properties, I receive the following error:-

Cannot resolve the collation conflict between
"Latin1_General _CI_AS"
and "SQL_Latin1_Gen eral_CP1_CI_AS" in the equal to operation.
(Microsoft SQL Server, Error: 468)

Some reference suggest that I can change the database collation by
clicking database properties!

What can I do?

That smells like a bug. But question is: how did you arrive here?

If I understand it right, you had an SQL 2000 instance that you upgraded to
SQL 2005?

What server collation did you have in SQL 2000? Did you select a different
collation when you upgraded?

Do you get this error with all databases, or only some?

What you could try is install the CTP of SP1, to see if the problem
is resolved, although my gut feeling says that it is not. You find the
CTP here:
http://www.microsoft.com/downloads/i...splayLang%3den
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Peter Nurse]

Thanks for your help, Erland.

That smells like a bug. But question is: how did you arrive here?
If I understand it right, you had an SQL 2000 instance that you upgraded to
SQL 2005? Yes, I upgraded from SS 2000. I didn't use the upgrade advisor because
either 1) I didn't know it was there or 2) it didn't work properly. I
forget which.
What server collation did you have in SQL 2000? Did you select a different
collation when you upgraded? I've never consciously selected a collation. I didn't know about them
until SS 2005!
Do you get this error with all databases, or only some? All databases including Northwind & Master neither of which I've
touched.
What you could try is install the CTP of SP1, to see if the problem
is resolved, although my gut feeling says that it is not. You find the
CTP here:

I think I might defer that until I'm really desperate!

[Erland Sommarskog]

Peter Nurse (Pt****@yahoo.c om.au) writes:

Do you get this error with all databases, or only some?

All databases including Northwind & Master neither of which I've
touched.

Could you run this and post the output:

select name, collation_name, compatibility_l evel from sys.databases
select serverproperty( 'Collation')

Assuming that all databases are in mode 80, try running
"sp_dbcmptl evel Northwind, 90" and see if this changes anything.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Peter Nurse]

>Could you run this and post the output:

select name, collation_name, compatibility_l evel from sys.databases
select serverproperty( 'Collation')
name collation_name
compatibility_l evel
----------------------------------------------------------------------------------------------------------
master Latin1_General_ CI_AS
80
tempdb Latin1_General_ CI_AS
90
model Latin1_General_ CI_AS
90
msdb
SQL_Latin1_Gene ral_CP1_CI_AS 90
pubs Latin1_General_ CI_AS
80
Northwind Latin1_General_ CI_AS
90
ASPProBU SQL_Latin1_Gene ral_CP1_CI_AS
80
ASPProWeb SQL_Latin1_Gene ral_CP1_CI_AS
80
ASPPro Latin1_General_ CI_AS
80

(9 row(s) affected)
----------------------------------------------------------------------------------------------------------
Latin1_General_ CI_AS

(1 row(s) affected)
Assuming that all databases are in mode 80, try running
"sp_dbcmptleve l Northwind, 90" and see if this changes anything.

Doesn't seem to fix anything.

[Erland Sommarskog]

Peter Nurse (Pt****@yahoo.c om.au) writes:

Could you run this and post the output:

select name, collation_name, compatibility_l evel from sys.databases
select serverproperty( 'Collation')

name collation_name
compatibility_l evel

Thanks for the output. Unfortunately, it did not reveal anything.

Are you familiar with the Profiler and can run a Profiler trace?
Start Profiler, and in the lower right check Show All Event Categories.
Then find "Errors and Warnings" and right-click to selecr the entire
event category. Now, right-click a database and select Properties.

This should reveal exactly which statement that bombs. If you are
uncertain on how to read the trace, save the trace to file, and
put it in a zip file and attach it a post, or just put it on a web
site and post a link.

Note that the trace will include a couple of Error 208. These are false
alarms, and should be ignored. We are looking for error 468.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Peter Nurse]

Erland, I was unable to post the trace file anywhere so I hope you
don't mind that I sent the trace to you by email.

However, this appears to be the offending part of the trace:-

SELECT
dtb.collation_n ame AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.data bases AS dtb
WHERE
(dtb.name=N'mas ter')

SELECT
dtb.collation_n ame AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.data bases AS dtb
WHERE
(dtb.name=N'mas ter')

select SERVERPROPERTY( N'servername')

select SERVERPROPERTY( N'servername')

Error: 468, Severity: 16, State: 9

Cannot resolve the collation conflict between "Latin1_General _CI_AS"
and "SQL_Latin1_Gen eral_CP1_CI_AS" in the equal to operation.

[Peter Nurse]

Erland, I was unable to post the trace file anywhere so I hope you
don't mind that I sent the trace to you by email.

However, this appears to be the offending part of the trace (the first
two select statements were actually repeated twice):-

SELECT
dtb.collation_n ame AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.data bases AS dtb
WHERE
(dtb.name=N'mas ter')

select SERVERPROPERTY( N'servername')

Error: 468, Severity: 16, State: 9

Cannot resolve the collation conflict between "Latin1_General _CI_AS"
and "SQL_Latin1_Gen eral_CP1_CI_AS" in the equal to operation.

Neither of the SELECT statements raise an error when I run then
separately.

[Greg D. Moore \(Strider\)]

"Peter Nurse" <Pt****@yahoo.c om.au> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

Could you run this and post the output:

select name, collation_name, compatibility_l evel from sys.databases
select serverproperty( 'Collation')

Out of curiousity, did you sp_detach_db/sp_attach_db the ASPProBU/Web DBs
from 2000->2005?

name collation_name
compatibility_l evel
-------------------------------------------------------------------------- -------------------------------- master Latin1_General_ CI_AS
80
tempdb Latin1_General_ CI_AS
90
model Latin1_General_ CI_AS
90
msdb
SQL_Latin1_Gene ral_CP1_CI_AS 90
pubs Latin1_General_ CI_AS
80
Northwind Latin1_General_ CI_AS
90
ASPProBU SQL_Latin1_Gene ral_CP1_CI_AS
80
ASPProWeb SQL_Latin1_Gene ral_CP1_CI_AS
80
ASPPro Latin1_General_ CI_AS
80

(9 row(s) affected)
-------------------------------------------------------------------------- -------------------------------- Latin1_General_ CI_AS

(1 row(s) affected)

Assuming that all databases are in mode 80, try running
"sp_dbcmptleve l Northwind, 90" and see if this changes anything.

Doesn't seem to fix anything.

[Peter Nurse]

Out of curiousity, did you sp_detach_db/sp_attach_db the ASPProBU/Web
DBs
from 2000->2005?

They were actually created from ASPPro which (prior) to some repair
efforts before posting the first message also had Collate =
SQL_Latin1_Gene ral_CP1_CI_AS.

I'm pretty sure I didn't detach & reattach (certainly not using the
named SPs), I think they just carried over from SS 2000.

Have a look at my 2nd response to Erland's post (No. 6) - this problem
is getting curiouser & curiouser . . .