Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Lat

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

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

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.name COLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName, Convert(varchar(1000),value) as colDescription

From (

Select table_name as colObjectName

From Openquery (yourservername,'yourdbname..sp_tables')

) procs

Left Join

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table', default, default, default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like 'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215