So today was one of those days - you know when you see a ton of mails saying everything that was working yesterday is not working today! But somehow you believe that you'd be able to make it all work again - almost magically!
Well the problem today was as follows: I work with a .NET application that has the following components:
1) A windows NT service
2) A set of UI tools (desktop apps) to administer the behaviour of the service
Now what happened is that one of our QA was trying to set up an environment from scratch using the App - installer (an MSI) and the database scripts provided. The database had to be created first followed by running all scripts in order to create all objects and insert default data. The rest of the configuration had to be done by using the UI. The application uses strongly typed DataSet to access the DB layer. After setup, the app started throwing an unhandled exception while trying to start. The exception stack trace referred to a "column not found" whilst the table had the column when I checked the database. I just happened to check the query being executed by the application normally by using the SQL server management studio - and lo - it indeed complained of the missing column! I then realized that if I spelled the column name in the same case as being shown in Object Explorer, the query did succeed.
On further investigation, when I checked the server properties ( Right Click the server node in object explorer -> Properties; Click "General" under Select a Page pane; Look for the property named "Server Collation"), the collation was listed as "Latin1_General_CS_AI". When I googled for this, I got to know that the "CS" stands for case sensitive. To change it to case-insensitive, one must use "CI"
You can change the collation at the database level as follows:
1. Right click on the database name in object explorer and click properties
2. Under "select a page" (right pane) click on "Options"
3. Change the collation drop down to the desired collation
4. click Ok.
You can also accomplish the same by running the script from the master database:
ALTER DATABASE
NOTE: The database needs to be exclusively locked to perform this operation. This, in plain English, means that there should not be any connections to the database while doing this.
The following link demonstrates how to do this at a query level:
Steven Smith : Case Sensitive or Insensitive SQL Query
If you use a collation with each query, you needn't worry about DB collation!
All the best!
No comments:
Post a Comment