Import Email from AD Mailbox

Question

We have an old legacy SQL 2000 server (the last in the farm) – We’ve been unable to get rid of this server as it uses xp_findnextmsg, xp_readmail etc to monitor a mailbox (via mapi) and import all email to that address into a database. The database contains simple tables that store "from", "to", "subject", "body", "Sent Date" & so on.

As you may know, the procs above are no longer in use in SQL 2005+

This table is read from dozens of internal systems, for instance emails to this mailbox can be automatically picked up by our helpdesk systems & create calls etc.

My question is this: what it the easiest / modern way of doing this in SQL 2008+? Is it going to be a case of writing a .net binary / service that will use smtp or something to connect to a mailbox and insert the data into SQL or is there a simpler way to do it? (SSIS / 3rd party tools / pre-existing code / projects?)

Just thought I’d ask before i start writing something – no point re-inventing the wheel as it were.

PS: The Mailbox in question is an exchange 2010 mailbox.

Edit: This functionality was hinted to be re-introduced in 2008 & dbmail: http://connect.microsoft.com/SQLServer/feedback/details/126167/xp-readmail-replacement-for-sql-2005 but it looks like it failed to materialise!

Edit 2: I've just found a decent code sample here that utilises the new web services in exchange 2007+: http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/dd2b465b-b1d2-4c0d-82ec-c36c6c482d5d - experimenting in progress (has anyone ever worked with SQL and the Exchange web services?)

Edit 3: All done! I knocked up a .net service that sits on our exchange server and monitors a mailbox & pushes any new mail into SQL. Incase others have a similar question and need some sample code to get started - here is some rough code (chopped out of my service - replaced parameterised SQL with basic dynamic SQL for easy reading): (Note: you’ll need the EWS API 1.1 dll)

Imports Microsoft.Exchange.WebServices.Data

Dim ExchangeUrl As String = "https://DOMAIN.co.uk/ews/exchange.asmx"
Dim service As New ExchangeService(ExchangeVersion.Exchange2010_SP1)
service.Url = New Uri(ExchangeUrl)
service.Credentials = New WebCredentials("[email protected]", "PASSWORD")
Dim findResults As FindItemsResults(Of Item) = service.FindItems(WellKnownFolderName.Inbox, New ItemView(1000))
If findResults.Count > 0 Then
    service.LoadPropertiesForItems(findResults.Items, New PropertySet(BasePropertySet.FirstClassProperties))
End If

For Each item As Item In findResults.Items
    Dim CurrentEmail As EmailMessage = item

    '#### Grab Email Information
    E_ID = CurrentEmail.InternetMessageId.ToString()
    If CurrentEmail.Sender.Address.ToString() <> "" Then
        E_From = Replace(CurrentEmail.Sender.Address, "'", "''")
    Else
        E_From = Replace(CurrentEmail.Sender.Name, "'", "''")
    End If
    E_From = Replace(CurrentEmail.Sender.Address, "'", "''")
    E_To = Replace(CurrentEmail.DisplayTo, "'", "''")
    E_CC = Replace(CurrentEmail.DisplayCc, "'", "''")
    E_Subject = Replace(CurrentEmail.Subject, "'", "''")
    E_Body = Replace(CurrentEmail.Body.Text, "'", "''")
    E_Received = CurrentEmail.DateTimeReceived.ToString("dd/MM/yyyy HH:mm:ss")
    E_Sent = CurrentEmail.DateTimeSent.ToString("dd/MM/yyyy HH:mm:ss")

    '#### Save the email into SQL
    If SqlQuery("INSERT INTO tbl_Emails ([MessageID], [From], [To], [CC], [Subject], [Body], [Received], [Sent]) VALUES ('" & E_ID & "', '" & E_From & "', '" & E_To & "', '" & E_CC & "', '" & E_Subject & "', '" & E_Body & "', CONVERT(DATETIME, '" & E_Received & "', 103), CONVERT(DATETIME, '" & E_Sent & "', 103))") = True Then
        item.Delete(DeleteMode.HardDelete)
    End If
Next

Answer 1

The first thing that comes to mind for me is SQL CLR. MAPI is not specifically supported in .net (at least to my knowledge), although there are work arounds. Reading from an exchange mailbox, luckily, is supported in host of situations.

BTW,I've found working with email in .net to be relatively pain free.