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_General_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?

Peter Nurse (PtrNrs@yahoo.com.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_General_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, esquel@sommarskog.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

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!

Peter Nurse (PtrNrs@yahoo.com.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_level from sys.databases
select serverproperty('Collation')

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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

>Could you run this and post the output:

> select name, collation_name, compatibility_level from sys.databases
> select serverproperty('Collation')

name collation_name
compatibility_level
----------------------------------------------------------------------------------------------------------
master Latin1_General_CI_AS
80
tempdb Latin1_General_CI_AS
90
model Latin1_General_CI_AS
90
msdb
SQL_Latin1_General_CP1_CI_AS 90
pubs Latin1_General_CI_AS
80
Northwind Latin1_General_CI_AS
90
ASPProBU SQL_Latin1_General_CP1_CI_AS
80
ASPProWeb SQL_Latin1_General_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_dbcmptlevel Northwind, 90" and see if this changes anything.

Doesn't seem to fix anything.

Peter Nurse (PtrNrs@yahoo.com.au) writes:
>>Could you run this and post the output:
>
>> select name, collation_name, compatibility_level from sys.databases
>> select serverproperty('Collation')
>
> name collation_name
> compatibility_level

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, esquel@sommarskog.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

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_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=N'master')

SELECT
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=N'master')

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_General_CP1_CI_AS" in the equal to operation.

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_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=N'master')

select SERVERPROPERTY(N'servername')

Error: 468, Severity: 16, State: 9

Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

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

"Peter Nurse" <PtrNrs@yahoo.com.au> wrote in message
news:1143488444.248281.213610@v46g2000cwv.googlegr oups.com...
> >Could you run this and post the output:
>
> > select name, collation_name, compatibility_level 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_level
> --------------------------------------------------------------------------
--------------------------------
> master Latin1_General_CI_AS
> 80
> tempdb Latin1_General_CI_AS
> 90
> model Latin1_General_CI_AS
> 90
> msdb
> SQL_Latin1_General_CP1_CI_AS 90
> pubs Latin1_General_CI_AS
> 80
> Northwind Latin1_General_CI_AS
> 90
> ASPProBU SQL_Latin1_General_CP1_CI_AS
> 80
> ASPProWeb SQL_Latin1_General_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_dbcmptlevel Northwind, 90" and see if this changes anything.
>
> Doesn't seem to fix anything.
>

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_General_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 . . .