Error whilst processing ORDER BY clause

Stuart M · Joined: 06 Aug 2007 Posts: 3

Our application is trying to perform the following SQL SELECT query against a folder in the Exchange 2003 mailbox store using the documented "ADO.Recordset" method over the "ExOleDB.Datasource" provider:

"SELECT "DAV:href", "DAV:displayname", "urn:schemas:httpmail:read" FROM SCOPE('shallow traversal of "http://server/administrator/inbox"') WHERE "DAV:isfolder" = False AND "DAV:ishidden"= False ORDER BY "urn:schemas:httpmail:read" ASC"

We receieve the error "Error whilst processing ORDER BY clause in the SQL statement", HRESULT = 0x8055002b, when the statement is executed.
If we remove the "ORDER BY" clause it succeeds, or change the property name in the clause to reference a string or int data type it also succeeds, but it refuses to sort by the boolean "urn:schemas:httpmail:read" property. Is this a limitation in the provder or information store? Can we use a CAST to perform the search or another method entirely? We want to order message item in a folder display by several properties, the first being wether the item is read so that unread items always appear at the top.

Any help on this issue would be appreciated.
Stuart M

Archived from group: microsoft>public>exchange2000>applications

Tom Rizzo [MSFT] · Joined: 05 Aug 2007 Posts: 13

My guess would be that you need to CAST it to a boolean since it is assuming
it's a string.

Tom

--
Looking for a good book on programming Exchange, Outlook, ADSI and
SharePoint? Check out http://www.microsoft.com/MSPress/books/5517.asp

This posting is provided "AS IS" with no warranties, and confers no rights.

"Stuart M" wrote in message@microsoft.com...
> Our application is trying to perform the following SQL SELECT query
against a folder in the Exchange 2003 mailbox store using the documented
"ADO.Recordset" method over the "ExOleDB.Datasource" provider:
>
> "SELECT "DAV:href", "DAV:displayname", "urn:schemas:httpmail:read" FROM
SCOPE('shallow traversal of "http://server/administrator/inbox"') WHERE
"DAV:isfolder" = False AND "DAV:ishidden"= False ORDER BY
"urn:schemas:httpmail:read" ASC"
>
> We receieve the error "Error whilst processing ORDER BY clause in the SQL
statement", HRESULT = 0x8055002b, when the statement is executed.
> If we remove the "ORDER BY" clause it succeeds, or change the property
name in the clause to reference a string or int data type it also succeeds,
but it refuses to sort by the boolean "urn:schemas:httpmail:read" property.
Is this a limitation in the provder or information store? Can we use a CAST
to perform the search or another method entirely? We want to order message
item in a folder display by several properties, the first being wether the
item is read so that unread items always appear at the top.
>
> Any help on this issue would be appreciated.
> Stuart M
>

Stuart M · Joined: 06 Aug 2007 Posts: 3

The property "urn:schemas:httpmail:read" is of type boolean, so I have tried to cast it to a string in the ORDER BY using CAST("urn:schemas:httpmail:read" AS "string"), the statement then executes but the results are incorrectly ordered. (I also tried casting to int and boolean with no luck). If you add this value to the property list at the top of the SELECT statement the reason comes out, the cast always returns NULL. So its not possible to cast a boolean value in this way.
I did find another post on the same issue in MS SQL server 2000, this suggested you could use a workaround invloving the CASE clause, ie. SELECT CASE WHEN "urn:schemas:httpmail:read" THEN 1 ELSE 0 END AS , ..........., ORDER BY , but it appears this is not supported by the Exchange provider. So I'm still stuck on this issue.
Stuart

Stuart M · Joined: 06 Aug 2007 Posts: 3

The property "urn:schemas:httpmail:read" is of type boolean, so I have tried to cast it to a string in the ORDER BY using CAST("urn:schemas:httpmail:read" AS "string"), the statement then executes but the results are incorrectly ordered. (I also tried casting to int and boolean with no luck). If you add this value to the property list at the top of the SELECT statement the reason comes out, the cast always returns NULL. So its not possible to cast a boolean value in this way.
I did find another post on the same issue in MS SQL server 2000, this suggested you could use a workaround invloving the CASE clause, ie. SELECT CASE WHEN "urn:schemas:httpmail:read" THEN 1 ELSE 0 END AS , ..........., ORDER BY , but it appears this is not supported by the Exchange provider. So I'm still stuck on this issue.
Stuart

Error installing 'Application Analyzer for Domino' Dear all, I installed 2.0, MSDE 2000 A and setup an instance of MSDE with [setup Error on starting the analyzer: "Invalid connection stringInvalid connection system failed to initialize" After deleting

Error in OWA - An error occured processing your Junk Email L I've just backup and restore my SBS exchange from one server to another. Both of them are using the same NetBIOS. After restoring, there is an error occured when accessing some mailboxes by OWA. "An error occured processing your Junk Email Lists. You are

Clear Sort Order I have a journal mailbox that has over 100K messages in it.. .I inadvertantly selected a sort by sender and since then I have not been able to access the mailbox... I have a mail client that has been trying to open the mailbox for two days now.. solid.. I

Global Address Book Order I have just migrated from Exchange 5.5 single server to Exchnage 2003 Clusterd Enviroment. The order in the Exchange 5.5 of Email Addres is and in the adress book it shows as LastName, FirstName In Exchange 2003 the Email A

OL2002 How To control the order of address books in the Sho In OL2002 is there a way to control the order that the address books are displayed in the "Show Names From list" ? I have tried to follow the procedure at and this does not seem to apply in OL2002. I w