4

I had this error and I don't know how to fix it

Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

note: I already change the collation from the database option --> Collation

I change it from "Arabic_CI_AS" to "SQL_Latin1_General_CP1_CI_AS"

and I am still getting the same error !!
Any suggestion to solve this ?

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
HAJJAJ
  • 3,667
  • 14
  • 42
  • 70

1 Answers1

9

The database collation applies only when you create NEW objects without specifying the collation.

When you change it from "Arabic_CI_AS" to "SQL_Latin1_General_CP1_CI_AS", all the textual columns in the database are still collated Arabic_CI_AS. You can check this using 

select object_name(object_id), name, collation_name
from sys.columns
where collation_name like '%Arabic%'

A patch to this problem is to put COLLATE DATABASE_DEFAULT against the comparison, e.g.

SELECT *
FROM TBL1
INNER JOIN TBL2 on X = Y COLLATE DATABASE_DEFAULT

or

SELECT *
FROM TBL1
WHERE X = Y COLLATE DATABASE_DEFAULT

etc

There is a script on this site that attempts to change the collation across an entire database, but

  1. I have not personally tried it
  2. Make sure you have a good backup of your database before trying it
  3. It doesn't look like it will handle complex databases with indexed views, foreign key/default constraints etc
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Also new temporary tables use tempdb's collation, not the current database's collation. – MartW Mar 17 '11 at 08:16
  • when i run select object_name(object_id), name, collation_name from sys.columns where collation_name like '%Arabic%' it return no data at all!! but when i change it to select object_name(object_id), name, collation_name from sys.columns where collation_name like '%SQL_Latin1_General_CP1_CI_AS%' it is returning all data ! so where is the problem ?? and i used this one also alter database dbname collate SQL_Latin1_General_CP1_CI_AS but no progress !!! – HAJJAJ Mar 17 '11 at 08:56
  • @HAJJAJ, in that case, try this `select databasepropertyex('master', 'collation')` It could be the server(and tempdb) is Arabic, and the database is latin. – RichardTheKiwi Mar 17 '11 at 10:21
  • ahm ahm... may i know how to change this one ?!! – HAJJAJ Mar 17 '11 at 11:24
  • @HAJJAJ It is a mess. Usually, I just create a new instance using the same collation as the database, and attach to it there. To make specific queries work, use `collate database_default` as shown above – RichardTheKiwi Mar 17 '11 at 11:27
  • well ,i am really very grateful for your help , and forgave me for wasting your time thank you very much. that is the answer i was looking for it since three months . – HAJJAJ Mar 17 '11 at 11:37