Monday, October 30, 2006

SQL Server 2005 Collation Issue

I ran into this strange behavior of SQL Server 2005, I have Asp.Net 2.0 Application that is connecting to a SQL 2000 db with collation Arabic_CI_AS, in the application i run a SQL query like follows

Select * from Users where UserName like '%احمد%'

the query works as expected and returns the result, then i modified the application to connect to a SQL Server 2005 database with collation SQL Latin collation and the query no longer returns results, i checked and made sure that the column UserName has a collation Arabic_CI_AS (to be like the SQL 2000 db) and still no results was returned.

I had to change the collation of the whole database to be Arabic_CI_AS (which is a new feature in SQL 2005), after that the query worked as expected.

No comments: