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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Peter Nurse (PtrNrs@yahoo.com.au) writes:[color=blue]
> 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?[/color]

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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Thanks for your help, Erland.
[color=blue]
>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?[/color]
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.
[color=blue]
>What server collation did you have in SQL 2000? Did you select a different
>collation when you upgraded?[/color]
I've never consciously selected a collation. I didn't know about them
until SS 2005!
[color=blue]
>Do you get this error with all databases, or only some?[/color]
All databases including Northwind & Master neither of which I've
touched.
[color=blue]
>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:[/color]
I think I might defer that until I'm really desperate!

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Peter Nurse (PtrNrs@yahoo.com.au) writes:[color=blue][color=green]
>>Do you get this error with all databases, or only some?[/color]
>
> All databases including Northwind & Master neither of which I've
> touched.[/color]

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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

>Could you run this and post the output:
[color=blue]
> select name, collation_name, compatibility_level from sys.databases
> select serverproperty('Collation')[/color]

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)


[color=blue]
>Assuming that all databases are in mode 80, try running
>"sp_dbcmptlevel Northwind, 90" and see if this changes anything.[/color]

Doesn't seem to fix anything.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Peter Nurse (PtrNrs@yahoo.com.au) writes:[color=blue][color=green]
>>Could you run this and post the output:[/color]
>[color=green]
>> select name, collation_name, compatibility_level from sys.databases
>> select serverproperty('Collation')[/color]
>
> 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

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.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

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.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

"Peter Nurse" <PtrNrs@yahoo.com.au> wrote in message
news:1143488444.248281.213610@v46g2000cwv.googlegr oups.com...[color=blue][color=green]
> >Could you run this and post the output:[/color]
>[color=green]
> > select name, collation_name, compatibility_level from sys.databases
> > select serverproperty('Collation')[/color][/color]


Out of curiousity, did you sp_detach_db/sp_attach_db the ASPProBU/Web DBs
from 2000->2005?
[color=blue]
>
> name collation_name
> compatibility_level
> --------------------------------------------------------------------------[/color]
--------------------------------[color=blue]
> 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)
>
>
> --------------------------------------------------------------------------[/color]
--------------------------------[color=blue]
> Latin1_General_CI_AS
>
> (1 row(s) affected)
>
>
>[color=green]
> >Assuming that all databases are in mode 80, try running
> >"sp_dbcmptlevel Northwind, 90" and see if this changes anything.[/color]
>
> Doesn't seem to fix anything.
>[/color]

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

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_CP1_CI_AS)?

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Erland,

Third reply to a single post - is that a record? I've done a lot of
research while you've all been been sleeping in Europe!

I completely uninstalled & reinstalled SS 2005 (rather than upgrading
from SS 2000) and then attached my databases. Problem disappeared.
I'm sure the problem was to do with msdb which was
SQL_Latin1_General_CP1_CI_AS but now is Latin1_General_CI_AS.

My databases remain SQL_Latin1_General_CP1_CI_AS but it no longer seems
to be a problem.

Thanks very much for your assistance.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Peter Nurse (PtrNrs@yahoo.com.au) writes:[color=blue]
> I completely uninstalled & reinstalled SS 2005 (rather than upgrading
> from SS 2000) and then attached my databases. Problem disappeared.
> I'm sure the problem was to do with msdb which was
> SQL_Latin1_General_CP1_CI_AS but now is Latin1_General_CI_AS.
>
> My databases remain SQL_Latin1_General_CP1_CI_AS but it no longer seems
> to be a problem.[/color]

So you got everything working? That's great to hear!

Uninstalling SQL 2005 and reinstalling a fresh install, was probably the
only way out. I was completely baffled to see SELECT serverproperty() to
fail. It seemed impossible to understand what was going on. But my guess
is that the system databases were SQL_Latin1_General_CP1_CI_AS originally,
but that the upgrade somehow changed them to Latin1_Genereal_CI_AS. I don't
think that msdb was the main culprit, but rather master itself.

I will play around a little, and see if I can reconstruct at least part of
the problem. And at least I will report it internally to Microsoft, so that
they know about it. It's difficult to submit a proper bug report, though,
as that would require a complete repro. I have a feeling that the origin of
the problem may have been some funky setting in your original master
database, and that is difficult to recover now.

As for the missing GetDBVersion, that appears to be related to Reporting
Services, and it came from a different spid that ran queries for the
Database Properties dialog.


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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Thanks, again, for your great assistance.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Your problem is that TempDB is using a different collation than your
application DBs. When performing operations where TempDB is used (such
as JOINs or ORDER BYs) on char/vchar columns you'll get this error
message because the result set doesn't match the collation order of
the application DB. THe only way I know how to correct this is to
create a new database, migrate the data from the old DB to the new DB,
drop the old DB and then rename the new DB to the same name as the old
one. You can also BCP out the data, drop and rebuild the database and
then BCP the data back in, but most folks don't know how to use BCP.

Brad

On 27 Mar 2006 11:40:44 -0800, "Peter Nurse" <PtrNrs@yahoo.com.au>
wrote:
[color=blue][color=green]
>>Could you run this and post the output:[/color]
>[color=green]
>> select name, collation_name, compatibility_level from sys.databases
>> select serverproperty('Collation')[/color]
>
>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)
>
>
>[color=green]
>>Assuming that all databases are in mode 80, try running
>>"sp_dbcmptlevel Northwind, 90" and see if this changes anything.[/color]
>
>Doesn't seem to fix anything.[/color]

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

> but most folks don't know how to use BCP

.. . . and some folks don't even know what BCP is (are?).

Nevertheless, I solved the problem by uninstalling & reinstalling SS
2005.

Thanks for your contribution.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Peter Nurse (PtrNrs@yahoo.com.au) writes:
[color=blue][color=green]
>> but most folks don't know how to use BCP[/color]
>
> . . . and some folks don't even know what BCP is (are?).[/color]

BCP = Bulk CoPy.

A program to load data files quickly in and out of SQL Server. Sort of
the bare core of DTS if you like.


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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9795ED7DDC729Yazorman@127.0.0.1...[color=blue]
> Peter Nurse (PtrNrs@yahoo.com.au) writes:
>[color=green][color=darkred]
> >> but most folks don't know how to use BCP[/color]
> >
> > . . . and some folks don't even know what BCP is (are?).[/color]
>
> BCP = Bulk CoPy.[/color]

Hmm, I've always figured it was Bulk Copy Program. :-)

Either case, very useful.
[color=blue]
>
> A program to load data files quickly in and out of SQL Server. Sort of
> the bare core of DTS if you like.
>
>
> --
> 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[/color]

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Thanks, Erland, for rescuing me once again.

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:[color=blue]
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns9795ED7DDC729Yazorman@127.0.0.1...[color=green]
>> BCP = Bulk CoPy.[/color]
>
> Hmm, I've always figured it was Bulk Copy Program. :-)[/color]

I guessed on Bulk CoPy, since the copy command on Unix - where BCP and
SQL Server after all has its roots - is "cp".

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

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

I am running into the same problem.

We have a server that was running SQL 2000. Complete uninstall of 2000.
Complete fresh install of SQL 2005. Then we used the 2000 to 2005
migration wizard to copy the app databases from another 2000 server to
the new 2005 server.

When I run these statements:
select name, collation_name, compatibility_level from sys.databases
select serverproperty('Collation')

I get these results:

master Latin1_General_CI_AI 90
tempdb Latin1_General_CI_AI 90
model Latin1_General_CI_AI 90
msdb Latin1_General_CI_AI 90
appdb1 SQL_Latin1_General_CP1_CI_AS 90
appdb2 SQL_Latin1_General_CP1_CI_AS 90

Latin1_General_CI_AI

We get errors in our app because we use tempDB for some ## temp tables.


On our development server, the system databases are all
SQL_Latin1_General_CP1_CI_AS and the serverproperty('Collation') also
returns SQL_Latin1_General_CP1_CI_AS. So Im not sure what the best way
to proceed is. Should I just create new databases on production to
match the Latin1 collation? If we bring those databases down to
development, will be have problems on development with unmatched
collation errors?

Tim

re: SQL Server 2005: Collation Conflict Error when selecting Database Properties

(sdwebguy@gmail.com) writes:[color=blue]
> We have a server that was running SQL 2000. Complete uninstall of 2000.
> Complete fresh install of SQL 2005. Then we used the 2000 to 2005
> migration wizard to copy the app databases from another 2000 server to
> the new 2005 server.
>
> When I run these statements:
> select name, collation_name, compatibility_level from sys.databases
> select serverproperty('Collation')
>
> I get these results:
>
> master Latin1_General_CI_AI 90
> tempdb Latin1_General_CI_AI 90
> model Latin1_General_CI_AI 90
> msdb Latin1_General_CI_AI 90
> appdb1 SQL_Latin1_General_CP1_CI_AS 90
> appdb2 SQL_Latin1_General_CP1_CI_AS 90
>
> Latin1_General_CI_AI
>
> We get errors in our app because we use tempDB for some ## temp tables.
>
>
> On our development server, the system databases are all
> SQL_Latin1_General_CP1_CI_AS and the serverproperty('Collation') also
> returns SQL_Latin1_General_CP1_CI_AS. So Im not sure what the best way
> to proceed is.[/color]

First you should make a decision on which collation you want to
use. SQL collations gives somewhat better performance they say, but
I believe Windows collations gives somewhat better linguistic behaviour.

But since the difference between SQL_Latin1_General_CP1_CI_AS and
Latin1_General_CI_AI may not be significant to you, and it's a bit
of a hassle to change the collation for all objects in a database, the
is probably to uninstall SQL 2005, and then reinstall, this time
paying more attention to the collation dialogue. After this you would
redo the migration.

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