Collation error when querying on meta-views / system tables 1

[Catadmin]

Has anyone else seen this? I've googled this, but there seems to be no reason "why" so far. And that's what I'm trying to find, the reason why this happens. I've already got a work around.

Running the following query:

USE MyDB

Select o.[name] as TableName,
Case When e.[value] is Null or e.[value] = ' ' Then 'Table Descrip N/A' 
When e.[value] is not Null Then cast (e.[value] as varchar (3000))
	End as TableDescription
from sysobjects o with (nolock) 
left join
::fn_listextendedproperty('MS_Description','user','dbo','table', default, null, null) e 
				on o.[name] = e.objname
 			where o.xtype = 'U' --and 
				--o.[name] = LTrim(RTrim(@tblName))
	Order by o.[name]

..comes up with the below error message:

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.

Now, as none of my databases, System DBs included, are set to "Latin1_General_CI_AI", I cannot think as to why SSMS would be trying to do a conversion from the _AS collation to the _AI collation. Nothing in my query requests a collation conversion and it shouldn't have.

As I said above, I have a workaround I found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177141&SiteID=1,

but my question is, why should I have to use a work around? Why won't it work as written?

If anyone has any specific knowledge or random thoughts you'd like to share on this issue, I'd love to hear them.

Thanks,


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

The functoin fn_listextendedproperty forces the collation Latin1_General_CI_AS within it's code. This will require you to COLLATE the data types between Latin1_General_CI_AI and Latin1_General_CI_AS.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Thanks, Denny. This was driving me nuts, but that explains the problem. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com