help-Cannot resolve collation conflict for equalto

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 2000 Forums

SQL Server Development (2000)

help-Cannot resolve collation conflict for equalto

Author

Topic

wanni
Starting Member

14 Posts

Posted - 2006-06-11 : 08:13:22

I keep getting "Cannot resolve collation conflict for equal to operation." error
here is my query
SELECT DISTINCT
Budget.Budget_ID, SUM(CONVERT(int,
REPLACE(LEFT(ef2kWeb.dbo.bel008_a.bel008_a019,
LEN(ef2kWeb.dbo.bel008_a.bel008_a019) - CHARINDEX('.',
REVERSE(ef2kWeb.dbo.bel008_a.bel008_a019))), ',', ''))) AS TotalCost,
ef2kWeb.dbo.bel008_a.bel008_a007, Budget.Budget, Budget.Remark,
Department.Department
FROM Budget INNER JOIN
ef2kWeb.dbo.bel008_a ON
Budget.Budget_ID = ef2kWeb.dbo.bel008_a.bel008_a010 INNER JOIN
Department ON
ef2kWeb.dbo.bel008_a.bel008_a007 = Department.Department_ID
WHERE (ef2kWeb.dbo.bel008_a.bel008_a009 = '2006') AND (Budget.Annual = '2006')
GROUP BY Budget.Budget_ID, ef2kWeb.dbo.bel008_a.bel008_a007, Budget.Budget,
Budget.Remark, Department.Department
ORDER BY ef2kWeb.dbo.bel008_a.bel008_a007
can anyone help?
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-11 : 10:35:13

looks like your current database collation is different from ef2kweb.

What you can do is convert from one to another.


SELECT DISTINCT
       Budget.Budget_ID, 
       SUM(CONVERT(int, REPLACE(LEFT(ef2kWeb.dbo.bel008_a.bel008_a019, 
                                LEN(ef2kWeb.dbo.bel008_a.bel008_a019) - CHARINDEX('.',
                                REVERSE(ef2kWeb.dbo.bel008_a.bel008_a019))), ',', ''))) AS TotalCost,
       ef2kWeb.dbo.bel008_a.bel008_a007, Budget.Budget, Budget.Remark,
       Department.Department
FROM   Budget INNER JOIN ef2kWeb.dbo.bel008_a 
ON     Budget.Budget_ID = ef2kWeb.dbo.bel008_a.bel008_a010 COLLATE collation_name
INNER JOIN Department 
ON     ef2kWeb.dbo.bel008_a.bel008_a007 COLLATE collation_name = Department.Department_ID
WHERE (ef2kWeb.dbo.bel008_a.bel008_a009 = '2006') AND (Budget.Annual = '2006')
GROUP BY Budget.Budget_ID, ef2kWeb.dbo.bel008_a.bel008_a007, Budget.Budget,
         Budget.Remark, Department.Department

ORDER BY ef2kWeb.dbo.bel008_a.bel008_a007

wanni
Starting Member

14 Posts

Posted - 2006-06-11 : 21:38:39

thanks ,

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 10:58:21

I Hate collation

I have the same poblem, but now I'm getting that it's not a valid colation, even though I cut and pasted it from a table script


COLLATE SQL_Latin1_General_CPI_CI_AS

What's wrong with that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 11:22:29

Im assuming that the temp table is creatyed with the collation of teh database, and the information_schema views are based on master?


SELECT DISTINCT c.[TABLE_SCHEMA], c.[TABLE_NAME], u.CONSTRAINT_NAME    
  FROM COMPDB.[INFORMATION_SCHEMA].[COLUMNS] c    
  JOIN ##Key_Column_usage u
    ON u.[TABLE_NAME]         = c.[TABLE_NAME]		COLLATE SQL_Latin1_General_CPI_CI_AS     
   AND u.[TABlE_SCHEMA]       = c.[TABLE_SCHEMA]	COLLATE SQL_Latin1_General_CPI_CI_AS     
   AND u.[COLUMN_NAME]        = c.[COLUMN_NAME]    	COLLATE SQL_Latin1_General_CPI_CI_AS
  JOIN COMPDB.[INFORMATION_SCHEMA].[table_constraints] t      
    ON u.[CONSTRAINT_NAME]    = t.[CONSTRAINT_NAME]	COLLATE SQL_Latin1_General_CPI_CI_AS		
 WHERE t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 11:30:41

We always explicitly use COLLATE statements in all Create Table (including normal tables, #TEMPs and @TableVar) columns that are String datatype.

That sorts out any that are created in TEMPDB, regardless of the actual collation of TEMPDB.

The only other thing we watch out for is comparison operators on columns from different databases


SELECT *
FROM DB1.dbo.MyTable AS T1
     JOIN DB2.dbo.Mytable AS T2
        ON T1.SomeVarchar = T2.SomeVarchar -- Potentially needs a forced Collation

but it sounds like you are doing all that?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 11:38:31

I received a database with a collatrion of

COLLATE SQL_Latin1_General_Pref_CPI_CI_AS

Which is not the standard out of the boc collation of

COLLATE SQL_Latin1_General_CPI_CI_AS

What I want to know what the collation is suppose to be

BOL uses Greek as an example

And I noticed I got the COLLATE backwards and fixed that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-27 : 12:10:37

I infered from the "help", it's suppose to be

COLLATE Latin1_General_CI_AS

So it now runs, but what happened to CPI?


SELECT DISTINCT c.[TABLE_SCHEMA], c.[TABLE_NAME], u.CONSTRAINT_NAME    
  FROM COMPDB.[INFORMATION_SCHEMA].[COLUMNS] c    
  JOIN ##Key_Column_usage u
    ON c.[TABLE_NAME]         = u.[TABLE_NAME]		COLLATE Latin1_General_CI_AS     
   AND c.[TABlE_SCHEMA]       = u.[TABLE_SCHEMA]	COLLATE Latin1_General_CI_AS     
   AND c.[COLUMN_NAME]        = u.[COLUMN_NAME]    	COLLATE Latin1_General_CI_AS
  JOIN COMPDB.[INFORMATION_SCHEMA].[table_constraints] t      
    ON t.[CONSTRAINT_NAME]    = u.[CONSTRAINT_NAME]	COLLATE Latin1_General_CI_AS		
 WHERE t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:02:36

Brett: You seem to be referring to something (which is presumably generating ##Key_Column_usage) that I maybe should be able to guess

, but I can't!

Might help to know what that is ...

I would have thought that forcing the collation on the comparisons would do the trick, regardless of what collation you used ... but it seems that changing from COLLATE SQL_Latin1_General_CPI_CI_AS to COLLATE Latin1_General_CI_AS on the comparisons has made a difference, is that right?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources