Collation Error! - 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

General SQL Server Forums

New to SQL Server Programming

Collation Error!

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-20 : 13:45:18

[code]update table1 set type ='Q', count =(select count(ID) from table2
where computer = COMPUTER_NAME and
table2.SYSTEM=10
AND area<>100
AND COMPUTER_NAME IN (select computer from #Pr where Hi>111 OR Low>333)
GROUP BY COMPUTER_NAME)
from table2 where machine = COMPUTER_NAME and
SYSTEM_TYPE=100
AND ZONE<>100
AND COMPUTER_NAME IN
(select computer from #Pr where Hit>111 OR LOW>333)
GO[/code]

I have the above script, I encountered this error:
[code]Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.[/code]

Any help/clue will be appreciated!

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 13:49:00

Change your where clause to

...
WHERE  MACHINE COLLATE SQL_Latin1_General_CP1_CI_AS = COMPUTER_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
       AND SYSTEM_TYPE = 100
...

That may not be the only place where there is a collation conflict. If there are others, you can do a similar thing to force the collation to one type on both sides.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-20 : 15:21:01

I have tried it, it does not resolve the the conflict tried to split the Query but still encounter the same issue!

Any alternative please?

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 15:29:30

It is collation conflict somewhere. Run this query (with the appropriate table names) to see the collation of each column and see where you are comparing or assigning columns with different collations. Once you find that force the collation to the same on both sides as I had described in my previous post.

SELECT
	TABLE_NAME,
	COLUMN_NAME,
	COLLATION_NAME
FROM
	INFORMATION_SCHEMA.[COLUMNS]
WHERE
	TABLE_NAME = 'Table1'

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-20 : 15:39:50

hey sunitabeck, thanks for your assistance.
can you believe that they both (columns from both tables) have the same collation in both tables!
I'm assuming we shouldn't get than error if they are the same.

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 15:49:07

I can't explain it, unless the error is coming from some other part of the code, or some other assignment or comparison operation. Check the collation of both sides in each place where you have a comparison or assignment. If that does not help much, if you simplify the query and test one small part at a time to see which part is causing the issue that might help.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-20 : 16:03:33

ok thanks, I found out that one of the columns that I'm assigning a value to it from another table has a collation of 'NULL'.
How can I change that to 'Latin1_General_BIN '?

thanks

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 20:59:44

Collation will be null for non-character columns - for example INT, FLOT etc. would have a collation of NULL. If it is a character column, it would have a collation.

If you are assigning a value to a column that has a null collation from a column that has a collation, that mean that you are assigning a character string to a numeric column. If the character string cannot be converted to that numeric type it would cause an error (although I don't think that is what is causing the issue here).

As far as I can see, you have only three tables here: table1, table2 and #Pr. If you can script the tables and post some sample data to populate the tables which would demonstrate the problem, that would make it easier for someone to see the problem.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-21 : 08:59:06

sunitabeck, RESOLVED, here's where the collation was needed:


update table1 set type ='Q', count =(select count(ID) from table2 
		where   computer = COMPUTER_NAME and
			table2.SYSTEM=10 
			AND area<>100 
			AND COMPUTER_NAME IN (select computer COLLATE Latin1_General_BIN from #Pr where Hi>111 OR Low>333)
	GROUP BY COMPUTER_NAME)
from table2 where machine = COMPUTER_NAME and
		SYSTEM_TYPE=100 
		AND ZONE<>100 
		AND COMPUTER_NAME IN 
		(select computer COLLATE Latin1_General_BIN from #Pr where Hit>111 OR LOW>333)
GO

Thanks

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-21 : 11:10:44

Aha! The COMPUTER_NAME IN (....) clause gets translated to COMPUTER_NAME = 'A' OR COMPUTER_NAME = 'B' OR COMPUTER_NAME = 'C' and so on. And obviously the collation conflict showed up then.

Glad you resolved it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources