InfiniTec - Henning Krauses Blog

Don't adjust your mind - it's reality that is malfunctioning

Exchange WebService ItemIds are case-sensitive

Every now and then people in the Exchange development forum asks whether the ItemId returned by a FindItems call is unique or not, because they allegedly got duplicate ids for one folder. The short answer to this question is: Yes, ItemIds are unique. And you are seeing duplicate ids because you are probably performing an incase-sensitive comparison.

Long answer:

In the following example, I executed a FindItems call on one of my mailbox folders and got multiple of such “duplicates”. One example:

AAMkAGY3OTBhZTFkLTAzNTgtNDM1Mi1hZjQ0LTg3YzUyOGRjM2I4NgBGAAAAAADssXJen7JoRp0FaJMBxQBjBwDTKvJ2qH1IS6bTjJryZwf0ACvTU4AKAADTKvJ2qH1IS6bTjJryZwf0ACvTWo5aAAA=

AAMkAGY3OTBhZTFkLTAzNTgtNDM1Mi1hZjQ0LTg3YzUyOGRjM2I4NgBGAAAAAADssXJen7JoRp0FaJMBxQBjBwDTKvJ2qH1IS6bTjJryZwf0ACvTU4AKAADTKvJ2qH1IS6bTjJryZwf0ACvTWo5AAAA=

These ids differ only in the 5th char from the right. The first one has a capital ‘A’ and the other has a small ‘a’ at that position. This can happen because the item id is base64 encoded. Base64 is way to represent binary data as ASCII text. A numerical value of 5 is encoded as a capital “F” and a value of 31 is also represented by an “f” - albeit a lower-case one.

If you store an item id in a database (SQL Server for example) you’ll most likely have run into the problem that a select statement with a WHERE clause on the item id returns multiple hits. This is due to the fact that the SQL Server by default uses a incase-sensitive collation for columns. To change this, open the table in question with the designer, select the column containing the item id. The designer will then display the column properties, like in this example:

Column Properties

Click on the Collation row and then on the button with the three full-stops. This will open the collation selection dialog:

Collation Selection

Ensure that the checkbox labeled “Case Sensitive” is checked. Click ok and then save the table.

Note: If you do this via the SQL Server Management Studio, it will recreate the entire table (including data, indexes and constraints). Depending on the size of the table, this can take quite some time.

Here is an SQL script which changes the column collation directly:

ALTER TABLE Example
    ALTER COLUMN ItemId VARCHAR(255)
    COLLATE Latin1_General_CS_AS

You need to adept the script to your environment. Albeit from the name of the table, you might have chosen a different data type for the ItemId column.

If you prefer not to change the table structure you can modify the query you are using to fetch the data from the database. Your query might now look like this:

SELECT * FROM Example 
    WHERE ItemId = 'AAMkAGY3OTBhZTFkLTAzNTgtNDM1Mi1hZjQ0LTg3YzUyOGRjM2I4NgBGAAAAAADssXJen7JoRp0FaJMBxQBjBwDTKvJ2qH1IS6bTjJryZwf0ACvTU4AKAADTKvJ2qH1IS6bTjJryZwf0ACvTWo5aAAA='

Instead of this query, use this one:

SELECT * FROM Example 
    WHERE ItemId COLLATE Latin1_General_CS_AS = 'AAMkAGY3OTBhZTFkLTAzNTgtNDM1Mi1hZjQ0LTg3YzUyOGRjM2I4NgBGAAAAAADssXJen7JoRp0FaJMBxQBjBwDTKvJ2qH1IS6bTjJryZwf0ACvTU4AKAADTKvJ2qH1IS6bTjJryZwf0ACvTWo5aAAA='

This select statement will perform a case-sensitive search on the table.


Technorati:

Posted by Henning Krause on Wednesday, August 24, 2011 9:10 PM, last modified on Wednesday, August 24, 2011 9:10 PM
Permalink | Post RSSRSS comment feed

Comments (2) -

On 6/21/2012 11:20:10 AM Lee wrote:

Lee

Nice information Henning, many thanks.

Reply

On 2/5/2014 3:24:29 PM Alexandr Emelin wrote:

Alexandr Emelin

Hello from Russia! Man, you saved my life! Really - we were debugging our application for 2 days - and then I have read this article. The case you describing here was exactly the root of all our problems. Thank you so much!!!!

Reply

Add comment

biuquote
  • Comment
  • Preview
Loading