Friday, April 03, 2009

Case Sensitive Searches in CRM

There have been a few instances that have come up where customers have requested the ability to make a certain field case sensitive. Typically this is for key fields, like foreign keys. But since the MSCRM database table collation is typically not set up for case sensitivity you don't get the option.

You can however set the collation at the column level and make just the field you want case sensitive.

Here are the steps:

1. Open Microsoft SQL Server Management Studio
2. Expand the <OrganizationName>_MSCRM
3. Pick the table you want (for example dbo.AccountBase) and expand the table
4. Expand the Columns folder
5. Right click on the column you want to set as being case sensitive
6. Choose Modify
7. In the right window panes make sure the field you want to change is highlighted
8. In the bottom pane of the right window (Column Properties) find the "Collation" property and click on the elipses in the right hand column to edit the property.
9. A new window will come up and you will see a checkbox for "Case Sensitive", check that box.
10. Click OK
11. Restart the SQL services
12. Just to be safe restart IIS

Now you're all set with a new case sensitive field.

Also, just to add the disclaimer. THIS IS NOT SUPPORTED by MS Support, so apply at your own risk. You have been warned. I have yet to see any issue arise from this, but my job is to make you aware.

David Fronk
Dynamic Methods Inc.

No comments:

Post a Comment