A business partner is emailing data to us, and I would like to extract it directly from the MS Exchange 2003 store via SQL Server 2005. I have my regular expressions in place to pull the data out of messages, but I still can’t get to the messages themselves! I spent all day yesterday chasing down threads of information, but haven’t yet put all the pieces together into a viable solution. Here’s the pertinent information:
- Exchange server: mail01.example.com
- SQL Server: sql01.example.com
- Exchange mail account: testresults@example.com
Using SQL Server Management Studio, I was able to create a linked server object to Exchange, as follows:
- Name: EXCHANGE
- Product: Exchange OLE DB provider
- Provider: Search.Collator.DSO
- Data_Source: ExOLEDB.DataSource
- Location: mail01.example.com
- Provider_String: Provider=ExOLEDB;Persist Security Info=True;Integrated Security=SSPI;Trusted_Connection=Yes;
- Catalog: NULL
SSMS says that I have successfully connected to the linked server.
Here’s where I am stumped: I have a test query to the Exchange store in SSMS as follows, just trying to retrieve sender addresses:
SELECT Convert(nvarchar(50), "urn:schemas:httpmail:from") Sender
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:httpmail:from"
FROM ".\ estresults\\inbox"')
SSMS returns the following message:
OLE DB provider "Search.CollatorDSO" for linked server "Exchange" returned message "Column does not exist.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT "urn:schemas:httpmail:from"
FROM ".\ estresults\\inbox"" for execution against OLE DB provider "Search.CollatorDSO" for linked server "Exchange".
Any thoughts as to what I’m doing wrong? I get the idea that I have not described the correct path to the user account\Inbox, but it could be ANYTHING else at this point.
TIA for your help!