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:

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:
Code SQL:
SELECT CONVERT(nvarchar(50), "urn:schemas:httpmail:from") Sender
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:httpmail:from" 
FROM ".\testresults\inbox"')
SSMS returns the following message:
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".
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!