sql server and exchage server 1

[molla]

Hi! all
I would like to keep track of email messages comming to a particular email address in a sql server table. I want some one to assist me in this regard.

best regards,

Molla

 

[jhall156]

What is your mail application, Outlook? Are you using Exchange? JHall

 

[molla]

Hi again,

I am using exchage server. The exchange server administrator created me an email account for this purpose.

thanks for the repely,

molla

 

[jhall156]

You can use SQL Mail stored procedures. Lookup sp_ProcessMail, xp_ReadMail et al in BOL. These stored procedures are designed to allow you to process email containing T-SQL Script and send the result sets back to the sender. This functionality can be bent to serve other purposes but the problem is this: if you plan on doing other things with the email message requiring you to identify the message to another application/component there's no way to easily pass this information. You can manipulate/inspect the mail item boolean property UnRead which is set to false when you use SQL Server's xp_ReadMail. Another caveat: Using xp_DeleteMail will not move your message to a deleted folder but rather send it winging into the abyss never to be seen again.
If you're only wanting to store information from the message then this should be easy and is well documented in BOL. Remember that you can return a result set with pertinent info about all the unread messages in your SQL Mail mailbox to a table variable and/or store it in a permanent table.

Do you have outlook installed on your SQL Server? If so check out the stored procedure sp_OACreate and the "see also" stored procedures in Books On-Line. You'll have to create more than one object i.e. Outlook.Application first and then a mapi namespace, then a folder, then a mail item etc. In other words you will have a number of int variables for these objects then you can set properties and invoke methods. JHall