Monday, November 26, 2007

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

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):
SELECT o.[id] AS tbl_id, o.[name] AS tbl_nm,
       x.value AS col_desc
  FROM sysobjects o (NOLOCK)
  INNER JOIN ::fn_listextendedproperty(
               N'MS_Description',
               N'user', N'dbo',
               N'table', N'MyTable',
               NULL, default) x
  ON x.objname = o.name
  WHERE o.name = 'MyTable'
The script returns an error message like this:

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.



Solution
: Rewrite your join like this

ON o.[name] = e.objname COLLATE Latin1_general_CI_AS

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

6 comments:

  1. Thanks! This solution works

    ReplyDelete
  2. Thanks for the solution..

    ehonor

    ReplyDelete
  3. Solution from Chandra Gunawan worked for me too.

    ReplyDelete
  4. Best solution on the web

    ReplyDelete
  5. works for me too. Thanks.

    ReplyDelete