Set Collate DATABASE_DEFAULT agaisnt where or in clause in SQL Server

By
Collate DATABASE_DEFAULT
I am not discuss collation here .Just focus on how to set collation default in SQL Server and why its need . Once a day i import a excel sheet in my database due to fetch out the data from my another table and excel import table with the help of join. Excel sheet have one sheet so i have import this in SQL server and set the name of table is ExcelImport.I discuss how to import Excel sheet in SQL Server later.
Now i have a table name is ExcelImport which has been imported by excel sheet .It have many column and city column is one of then and in this city is entered by name not by id.I have to extract all the data which are exist in our particular table having these ExcelImport . The simple query is given below


Code Sample Collate DATABASE_DEFAULT SQL Server


SELECT * FROM Organization
WHERE City_Name IN(
SELECT city FROM ExcelImport

)
Its looking fine and offcourse the query is fine but when i execute above query the following error has been occured

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I spend too much time on google to understand the issue then i find the solution and solution is we have to set Collation Database_Default.Its because collation of two different table column is different
Such type of error occur in following manner
1.In Where Clause
2.In Join Condition
3.In SQL Server 'IN' condition
3.When two database have different collation .

One solution is change the collation of database or table or column which was required Another way is set collate database_default in your query and the query is given below
SELECT * FROM Organization
WHERE City_Name COLLATE DATABASE_DEFAULT in(
SELECT city FROM ExcelImport

)
I have set the Collate  DATABASE_DEFAULT just next to the column name from Where Clause.If you are using where clause or join then u have to do same to fix the error just write the COLLATE DATABASE_DEFAULT after the where clause column name .

0 comments:

Post a Comment