SQL for Exchange 2000 the Bluffers Guide

Introduction

One of  the new features implemented within Exchange 2000 is Exoledb which is a server interface that allows you to access an Exchange database using ADO and OLEDB code (among other things). This gives you the ability to write standard SQL queries and have these run against mailboxes and public folders as if they where normal databases. However the Exchange 2000 Web storage system is not a conventional RDBMS system hence this guide is aimed at some of the uses and workarounds I have found working with SQL and Exoledb.

Accessing the Exchange Database with ADO

The first thing you need to understand when working with Exoledb is that the Exchange 2000 OLEDB provider is just that, its for use on an Exchange 2000 server only not from any clients or other W2K Servers. If you do want to access an Exchange databases from a client using ADO you can try using the Microsoft Internet Publishing provider msdaipp.dso which is a wrapper for DAV. This provider was released before Exchange 2000 so there is no guarantees that everything will work. Msdaipp.dso is installed with Windows 2000 SP1 and SP2

The next point to consider is the URL you use to access the web storage system. There are two types of URL's that can be used depending on wether you are accessing the store on the same server where you are executing the code. In this case you can use the file:// base URL scheme. If you are accessing mailboxes on another server you can use the Http:// URL scheme. Which is better ? for my mind the file based scheme is quicker if you are accessing local data, but if you want to access data on another server or from a client then Http is the only way to go. If you have any problems working out URL paths take a look at the M: drive on your server this gives you the your.domain.com name you need and also confirms what the mailbox alias is going to be.

Getting started with some code

To get started lets look at some examples of using ADO and Exoledb to access the web storage system. The simplest way to open a single item in the web storage system is to open up a record of that item. This first example shows how to open up an email as an ADO record.

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
rooturl = "file://./backofficestorage/your.domain.com/MBX/mailboxalias"
Conn.Open rooturl, ,3  ' root URL is the database you want to access
rec.open "file://./backofficestorage/your.domain.com/MBX/mailboxalias/inbox/test.eml"

An example of using client side code and the msdaipp.dso provider with the http URL space would be

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "MSDAIPP.DSO"
rooturl = "http://servername/exchange/mailboxname"
Conn.Open rooturl, ,3 ' root URL is the database you want to access
rec.open "http://servername/exchange/mailboxname/inbox/test.eml"

Using a Recordset and SQL

Opening WSS items as ADO records is nice but the real power of using exoledb is using SQL to create a recordset of items in the Web Storage System. So using the above example as a base to create a recordset you would need to add the following lines.

Set Rs = CreateObject("ADODB.Recordset")
workfolder = rooturl & "/inbox"
strView = "SELECT * "
strView = strView & "FROM scope ('shallow traversal of """& workfolder & """') "
strview = strview & " WHERE ""DAV:isfolder"" = false"
rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
rs.CursorType = 3
rs.open strview, rec.ActiveConnection, 1
rs.close

In this example I've created an updatable recordset of ADO items from the inbox, note you still need to create a record connection to the mail store first before performing the query to open the recordset. In this case you can set the record to something generic like the root of the mailbox or the inbox.

Authentication

Exoldb does not support authentication so any scripts that you want to run must be executed under the privileges of a user that has access to the mail database you are trying to access have a look at Q260771 for more information.

Content Type selecting the right objects

Exchange mailbox stores hold more then just email, these days with the advances in Outlook Web Access and the Installable File System some people even use their mailboxes for native file storage. So when your executing a SQL query against a mail store its good practice to specify the content type you are after when doing a query. To do this you specify a where clause for DAV:contentclass. For example to get only email messages it would be

Where "DAV:contentclass" = 'urn:content-classes:message'
 

Deep Traversal vs Shallow Traversal

In the from line of your SQL statement you have the option to set the scope of your SQL query. This is done using the SCOPE per dictate which has two traversal options which are deep and shallow. Deep transversals will search against the specified folder and all the sub folders of this folder, these type of queries are very resource intensive and are not recommend unless needed, if you are going to use deep traversals you should limit the amount of data returned with a where statement. Shallow traversal is the default option for a Exoldb query and if you don't specify a scope option your query will use this method, shallow traversals only query objects in the current folder. If you are for instance scanning through an entire mailbox most times its more efficient to do multiple shallow traversals then it is to do a large deep traversal (see this article for an example)

Note you cannot do a deep traversal of a MAPI store such as the default public folder.

Hierarchical Traversal

A Hierarchical traversal is a traversal that should be used if you only want to work out what a folder hierarchy  may be. For example you may be writing a script that is going to report on the complete content of a mailbox using multiple shallow traversals of all the mailbox subfolders once the folder hierarchy  has been determined using a hierarchy traversal. The hierarchical traversal uses a lot less server resources then doing a deep traversal to find the same information.

Working with Calenders

One of the more commonly done SQL query's in Exchange is a query for calendar appointments based on a certain time and date. The first thing you need to understand about doing this is "All appointments are stored in the Exchange store in coordinated universal time (UTC) or Greenwich mean time and are converted by the client program to the appropriate time zone". For example here in Sydney its +10 GMT if I query my exchange database with the date and time from the operating system my results are going to be out 10 hours on what I may want to see. To use a date and time in a SQL query it must be formatted in the ISO format which is yyy-mm-ddThh:mm:ssZ. You also need to Cast the date and time into the datetime data type in your SQL query.
 
To convert a time from your timezone to UTC Microsoft have written a sample function in the Exchange SDK called getdate. The theory behind this function is it uses the CDO object model to do the time zone conversion. It does this by creating a dummy appointment and setting the start time of this appointment to the time you want converted. Then by adjusting the time zone attribute of the appointment object and calling the CDO update method the start time of the appointment will then get updated to the adjusted time zone (Walla!). I have written a VBS sample of this function called ConvertUTC in my other article.

To convert a time into the ISO format using VBS is a little harder Microsoft in the Exchange SDK have provided a sample called getisdate using the VB function format. Unfortunately there is no equivalent to the format command under VBS so other methods must be used. In my other article I have used various date part functions and dateformat functions to get the the date into the required ISO format for a query. The proper ISO format for a  SQL query is yyyy-mm-ddThh:mm:ssZ if you are using VB just have a look at getisodate in the Exchange SDK.

The other thing you need to do when using date and times in your SQL queries is that you have to use the cast function to cast your ISO datetime into the datetime data type in the SQL query.  An example of this is

WHERE (""urn:schemas:calendar:dtend"" >= CAST("2000-09-01T01:00:00Z" as 'dateTime'))

The last thing you should be aware of when working with Calender appoitments is that if you have recurring meeting requests these will not appear in a ADO query unless you do a ADO Expansion Query. To make Exchange expand reoccurring appointments you must specify a time and date in the where clause of your query.

Advance SQL statements

Groupby is a SQL predicate that allows you to create a recordset that is sorted based on particular properties you specify in a select statement. Its particular useful for doing tally functions for example you may want to work out how many emails that you may have received from certain email address's. In the following example a recordset is opened of a users inbox and it will produce a text file showing all the email address's that a user has received email from and the number of emails received from each address. To work out item counts the DAV:visablecount property which is generated at execution time is used.

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\mailnumb.txt",2,true)
Conn.Provider = "ExOLEDB.DataSource"
rooturl = "file://./backofficestorage/your.domain.com/MBX/youruser1"
Conn.Open rooturl, ,3  ' root URL is the database you want to access
workfolder = rooturl & "/inbox"
rec.open workfolder
strView = "SELECT ""DAV:visiblecount"", ""urn:schemas:httpmail:from"" "
strView = strView & "FROM scope ('shallow traversal of """& workfolder & """') "
strview = strview & " WHERE ""DAV:isfolder"" = false"
strview = strview & " GROUP BY ""urn:schemas:httpmail:from"""
rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
rs.CursorType = 3
rs.open strview, Rec.ActiveConnection, 2
if rs.recordcount <> 0 then
	rs.movefirst
	while not rs.eof
		from1 = rs.fields("urn:schemas:httpmail:from").value
		number1 = rs.fields("DAV:visiblecount").value
		wfile.write from1 & " " & number1 & vbCrLf
	  	rs.movenext	
	wend
end if
rs.close

Joins (or lack of)

One of the things you can't do with Exoldb is use joins, joins are one of the things that gives a RDBMS database its relational functionality. There are a few things you can do with Exchange 2000 to try and mimic this behaviour. An example of this is if you may want to look at two inboxes and get a list of email address's that have sent mail to one mailbox but not the other. This is the basic idea of a unmatched query one way to do this is to open two recordsets one contains items in the first mailboxes inbox the second contains items from the second inbox. Then you can loop through the first recordset one at a time and search the second recordset using the find method to find the unmatched records here's an example.

Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Set Rec1 = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
Set Rs1 = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\mailnumb.txt",2,true)
Conn.Provider = "ExOLEDB.DataSource"
rooturl = "file://./backofficestorage/your.domain.com/MBX/youruser1"
rooturl1 = "file://./backofficestorage/your.domain.com/MBX/youruser2"
Conn.Open rooturl, ,3  ' root URL is the database you want to access
workfolder = rooturl & "/inbox"
workfolder1 = rooturl1 & "/inbox"
rec.open workfolder
rec1.open workfolder1
strView = "SELECT ""DAV:visiblecount"", ""urn:schemas:httpmail:from"" "
strView = strView & "FROM scope ('shallow traversal of """& workfolder & """') "
strview = strview & " WHERE ""DAV:isfolder"" = false "
strview = strview & " GROUP BY ""urn:schemas:httpmail:from"""
rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
rs.CursorType = 3
rs.open strview, Rec.ActiveConnection, 2
strview = replace(strview,"youruser1","youruser2")
rs1.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
rs1.CursorType = 3
rs1.open strview, Rec1.ActiveConnection, 2
if rs.recordcount <> 0 then
	rs.movefirst
	while not rs.eof
		from1 = rs.fields("urn:schemas:httpmail:from").value
		number1 = rs.fields("DAV:visiblecount").value
		rem df = msgbox(from1)
		Rs1.Find "urn:schemas:httpmail:from = '" & from1 & "'"
		while not rs1.eof			
			from2 = (rs1.fields("urn:schemas:httpmail:from").value)
			wfile.write from2 & vbCrLf
			rs1.movenext
		wend 
		rs.movenext	
	wend
end if
rs.close
rs1.close

Linking SQL 2000 and Exchange 2000

If Exchange 2000 and SQL 2000 are installed on the same physical server it is possible to link the 2 via SQL linked Servers and views. I've never tried to do this myself but if you want to do this have a look at this MSDN article and TechNet knowledge base article.