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: firstname.lastname@example.org
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:
SSMS returns the following message:Code SQL:SELECT CONVERT(nvarchar(50), "urn:schemas:httpmail:from") Sender FROM OpenQuery(Exchange, 'SELECT "urn:schemas:httpmail:from" FROM ".\testresults\inbox"')
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.Code SQL: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 ".\testresults\inbox"" FOR execution against OLE DB provider "Search.CollatorDSO" FOR linked server "Exchange".
TIA for your help!