Collation Conflict - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Collation Conflict

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2011-02-24 : 04:26:53

I am running a query to compare 2 sets of data. I used <> to show where the data doesnt compare.
Each time i do this i get an error message saying "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_Generel_CP1_CI_AS"

How can i resolve this?

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2011-02-24 : 05:37:54

I think it may have something to do with the data type. How can i convert one of the columns to make it nvarchar(80) so it will compare?

mandm
Posting Yak Master

120 Posts

Posted - 2011-02-24 : 11:24:20

It means the collation setting is different between the two tables for the column you're joining on. You can use a collation directive to solve this.

Example

ON tbl1.Column COLLATE SQL_Latin1_Generel_CP1_CI_AS = tbl2.Column

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2011-02-25 : 03:50:53

I am confused as to where the ON clause goes.

This is my SQL so far:

SELECT TOP (100) PERCENT JohnTest.dbo.TZ_TABLE.TZ_TABCODE, JohnTest.dbo.TZ_TABLE.TZ_FDESC,
nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.ReferenceCodeDesc
FROM JohnTest.dbo.TZ_TABLE INNER JOIN
nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode ON
JohnTest.dbo.TZ_TABLE.TZ_TABCODE COLLATE SQL_Latin1_General_CP1_CI_AS = nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.ReferenceCode
WHERE (JohnTest.dbo.TZ_TABLE.TZ_TABTYPE = N'fn') AND (nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.CodeType = 'ASSETCODE') AND
(JohnTest.dbo.TZ_TABLE.TZ_FDESC NOT LIKE nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.ReferenceCodeDesc)
ORDER BY JohnTest.dbo.TZ_TABLE.TZ_TABCODE

This is what i want to add in
ON JohnTest.dbo.TZ_Table.TZ_FDESC COLLATE SQL_Latin1_Generel_CP1_CI_AS = nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.ReferenceCodeDesc

Where does it go?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 03:57:23

(
JohnTest.dbo.TZ_TABLE.TZ_FDESC COLLATE SQL_Latin1_Generel_CP1_CI_AS
NOT LIKE nastest_7_copSSG_ParameterRule.dbo.prm_vw_ReferenceCode.ReferenceCodeDesc
)

KH
[spoiler]Time is always against us[/spoiler]

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2011-02-25 : 04:02:16

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources