February 28, 2007 at 1:24 pm
Hi,
This is a bit of a bit of a strange one. Basically I have a mailbox full of e-mail responses that I need to match to our SQL server database. I need to do a bit of processing of each of them before storing the e-mail in to the database along the correct account.
E-mails are constantly coming in and so too much batching would be a pain.
What I was hoping to do was setup the Exchange mailbox as a linked server (datasource) in the way you can in MS Access. We've already achieved what we need as a prototype in Access using linked tables to both Exchange and SQL Server.
I've read somewhere that there is an OLE DB provider for Exchange but it is only present on the Exchange server itself however I'm not keen on installing SQL Server on our exchange box to find out!
Has anyone ever attempted this before? One slight complication is that our SQL Server boxes are all on x64 now.
The only solution I can currently see which keeps all of this within a service is to purchase something like atmail (www.atmail.com) which can be configured to use SQL Server as its information store. I would then configure Exchange to forward e-mail to that box for any e-mails which require processing.
Cheers in advance,
Joel
February 28, 2007 at 2:41 pm
I don't think there is any difference between 32 bit and 64 bit...
Read the following which may help you in this...
http://msdn2.microsoft.com/en-us/library/aa213275(sql.80).aspx
MohammedU
Microsoft SQL Server MVP
March 1, 2007 at 8:41 am
Good luck!
We've spent years trying to get SQL and Exchange to work together. One of our big issues which you probably won't have to face is user-defined fields in Exchange records. In the end, the mechanism we used was a one-way transport from Exchange to SQL using the HTTP WebDAV protocol.
We found a third-party tool that is a desktop application that will migrate records bi-directionally between Exchange (actually from Outlook) and Access (or a linked data source such as SQL Server). It didn't fit our needs but may very well help you. The tool is DataLink for Outlook and runs about $700. You can find it at http://www.teamscope.com/otherpro/datalink.asp along with other Outlook tools. There is a trial available that runs for 30 days and is limited to batches of 50 records.
I can tell you that integrating with Exchange is a harrowing task with many different protocols (ExOLEDB, MAPI, Outlook Object model, WebDAV) that all have holes in what they do or where they can be used.
Good luck!
March 2, 2007 at 4:00 am
Thanks to both of you for your responses. They gave me some direction and I eventually stumbled across the Exchange 2003 SDK which actually describes the SQL syntax along with the limitations.
I hadn't appreciate that you only have SELECT access to Exchange which means the Exchange OLE DB provider is unsuitable as we need to MOVE e-mail from Exchange to SQL server before processing and summarising it (and then deleting it from SQL after a period of time).
One of my developers has knocked up something which uses WebDAV now which is kinda functional although it only SELECTs at the moment. We're hoping to roll this in to a series of CLR stored procedures.
I guess this is why Microsoft write apps like the MS CRM E-mail router as a service rather than connect straight from SQL server. It's all a bit messy, it would be nice if MS spent a bit of time in this area so developers can truly integrate their products. Personally I think WebDAV is a nasty protocol to use for the kind of bulk we're going to be dealing with.
Fingers crossed...
Thanks again,
Joel.
March 2, 2007 at 8:55 am
WebDAV is what we ended up using for two different applications. The learning curve is horrible and it doesn't seem to get any better. The inconsistent namespaces are what drove me nuts.
One was simply to fetch contact records from an Exchange Public Folder and place them into a SQL Server database for processing/loading into a new CRM tool.
The other application is close to what it sounds like you need. It is used to fetch e-mail messages from a mailbox and insert the contents into a database. The application then deletes the messages once they have been processed. We use this for collecting e-mails and putting them into a help desk ticketing system through the back door. The one issue we still have is that we haven't dealt with processing attachments. We created a workaround by simply moving the message to a special folder in the Exchange mailbox, pasting the text into our trouble ticket and including the Outlook Web Access URL into the message so that the agent can click and open the message to fetch/view the attachments.
If you are interested in code, let me know and I'll see what I can share. Specific questions are easiest to address.
September 27, 2007 at 10:31 pm
It'd be great to see any links that you have to even querying via WebDAV; I've been barking up wrong trees all day trying to to drill down into Public Folder contacts via LDAP and a linked server...
It seems that using Exchange OLE DB is out given that my exchange is on a different server to my sql server.
this stops me from using syntax like
FROM SCOPE(''shallow traversal of ''.\contacts
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply