SQL Server collation and Reporting Services behaviour
Recently I’ve came across another strange issue with reporting serivces.I’m hopping that all these stuff are well addressed in Yukon version ,or else 🙂 .I didn’t make the database case sensitive collation in the setup and case sensitive collation was not also activated in any of the columns throughout the database. In one of my tables, there is a column called “Client_Type” which is of type Varchar and unfortunately there are some bad data sitting there (might be because of wrong UI validations or data conversion or whatever).Below is different chunks of data in that column.
PRIMARY
PRIMARY MAKER
SECONDARY
Primary
Primary Maker
Secondary
From database standpoint, there is no difference between “Primary” and “PRIMARY” (which is obvious as I didn’t activate “case sensitive collation”),but when it comes to Reporting services it DOSE matter!! specifically in formulas and string manipulation functions. How come it is case sensitive in reporting services whilst is not in its backend database ?!!!
There is a possible workaround for this. Add ToString().ToUpper() to all field values used in comparisons. Of course changing the collation of the database is another possibility,but Just for your reference, specifying collation is usually done for the whole server in the installation of sql server and changing one database collation afterward is a bit tricky ,because if you have one database on a server which has a different collation to the server default, it will also have a different collation to TEMPDB. This can cause unexpected problems with sorting of results, and handling of #temp table data. Better, if you can, to get the whole server at the right collation.
