|
Home > Archive > Microsoft SQL Server forum > March 2006 > SQL Server 2005: Collation Conflict Error when selecting Database Properties
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Server 2005: Collation Conflict Error when selecting Database Properties
|
|
| Peter Nurse 2006-03-26, 8:26 pm |
| 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?
| |
| Peter Nurse 2006-03-27, 3:28 am |
| 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 2006-03-27, 7:37 am |
| 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
| |
| Peter Nurse 2006-03-27, 8:27 pm |
| >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.
| |
| Erland Sommarskog 2006-03-27, 8:27 pm |
| 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
| |
| Peter Nurse 2006-03-27, 8:27 pm |
| 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.
| |
| Peter Nurse 2006-03-27, 8:27 pm |
| 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.
| |
| Greg D. Moore \(Strider\) 2006-03-27, 8:27 pm |
|
"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?
[color=darkred]
>
> 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.
>
| |
| Peter Nurse 2006-03-28, 3:27 am |
| 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 . . .
| |
| Peter Nurse 2006-03-28, 3:27 am |
| 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)?
| |
| Peter Nurse 2006-03-28, 3:27 am |
| 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_C
P1_CI_AS but now is Latin1_General_CI_AS
.
My databases remain SQL_Latin1_General_C
P1_CI_AS but it no longer seems
to be a problem.
Thanks very much for your assistance.
| |
| Erland Sommarskog 2006-03-28, 3:27 am |
| Peter Nurse (PtrNrs@yahoo.com.au) writes:
> 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_C
P1_CI_AS but now is Latin1_General_CI_AS
.
>
> My databases remain SQL_Latin1_General_C
P1_CI_AS but it no longer seems
> to be a problem.
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_C
P1_CI_AS originally,
but that the upgrade somehow changed them to Latin1_Genereal_CI_A
S. 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Peter Nurse 2006-03-28, 3:27 am |
| Thanks, again, for your great assistance.
| |
| bradsbulkmail@comcast.net 2006-03-28, 11:29 am |
| 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:
>
>
>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.
| |
| Peter Nurse 2006-03-28, 8:26 pm |
| > 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.
| |
|
|
| Greg D. Moore \(Strider\) 2006-03-30, 3:25 am |
|
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9795ED7DDC72
9Yazorman@127.0.0.1...
> Peter Nurse (PtrNrs@yahoo.com.au) writes:
>
>
> BCP = Bulk CoPy.
Hmm, I've always figured it was Bulk Copy Program. :-)
Either case, very useful.
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Peter Nurse 2006-03-30, 3:25 am |
| Thanks, Erland, for rescuing me once again.
| |
| Erland Sommarskog 2006-03-30, 3:25 am |
| Greg D. Moore (Strider) (mooregr_deleteth1s@
greenms.com) writes:
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns9795ED7DDC72
9Yazorman@127.0.0.1...
>
> Hmm, I've always figured it was Bulk Copy Program. :-)
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
|
|
|
|
|