SQL Server 2005: Collation Conflict Error when selecting Database Properties

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_C
P1_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?

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: 
>
> 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('Coll
ation')

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>Could you run this and post the output:

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

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_C
P1_CI_AS               90
pubs                                          Latin1_General_CI_AS

80
Northwind                                   Latin1_General_CI_AS

90
ASPProBU                                 SQL_Latin1_General_C
P1_CI_AS
80
ASPProWeb                               SQL_Latin1_General_C
P1_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:[color=darkre
d] 
> 
>
> name                                         collation_name
>                    compatibility_level[
/color]

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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'ser
vername')

select  SERVERPROPERTY(N'ser
vername')

Error: 468, Severity: 16, State: 9

Cannot resolve the collation conflict between " Latin1_General_CI_AS
"
and " SQL_Latin1_General_C
P1_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'ser
vername')

Error: 468, Severity: 16, State: 9

Cannot resolve the collation conflict between " Latin1_General_CI_AS
"
and " SQL_Latin1_General_C
P1_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.googlegroups.com... 
> 


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_C
P1_CI_AS               90
> pubs                                          Latin1_General_CI_AS

>                         80
> Northwind                                   Latin1_General_CI_AS

>                       90
> ASPProBU                                 SQL_Latin1_General_C
P1_CI_AS
>            80
> ASPProWeb                               SQL_Latin1_General_C
P1_CI_AS
>           80
> ASPPro                                      Latin1_General_CI_AS

>                       80
>
> (9 row(s) affected)
>
>
> --------------------------------------------------------------------------
--------------------------------
>  Latin1_General_CI_AS

>
> (1 row(s) affected)
>
>
> 
>
> 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_C
P1_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 . . .

Erland,

In my 1st response to your post, I overlooked the fact that there was
an error calling GetDBVersion.

This is interesting because migration of a DTS package (to the SS 2005
version presumably) fails with the following error:-

"
The SaveToSQLServer method has encountered OLE DB error code
0x80040E14
(Could not find stored procedure 'msdb.dbo.sp_dts_putpackage'.).
The SQL statement that was issued has failed.
"

The trace has a number of occurences of "Could not find stored
procedure 'GetDBVersion'.".  I could send you the trace if it would
help.

Spooky, huh?  Could it be a problem with msdb (which BTW still has
Collate =  SQL_Latin1_General_C
P1_CI_AS)?