We have two forum databases on the same SQL server. Each forum has an associated email address for users to send posts to. Because SQL Mail can only read one Exchange client profile-thingy, both these email addresses deliver to this one box.
Each database runs its own copy of a stored procedure under a scheduled job. The stored procedure looks at all the mail in that box, and compares the sender's email address to the 'forum_users' table for the database that's running it. If there's a match, then it processes that email as a post on the forum.
Trouble is, if a user is registered on both forums, the stored procedure has no way of knowing which forum the email was intended for. 'Aha!', I thought, 'but xp_readmail can check the address that the email was sent TO!'
It doesn't though. What it returns is the name of the Exchange profile - like, when an email address looks like "Mr Person<email@example.com>" - it returns "Mr Person". And, like I said, both of these email addresses are delivering to the same profile. So xp_readmail has no way of telling between an email sent to "firstname.lastname@example.org" and "email@example.com"
This is, TBH, a bit of a pain. Is there no way for xp_readmail to return the resolved 'To' address?
I'm not sure I understand. Wouldn't that mean that every time someone wanted to submit a new post to one of the forums, they'd have to fill in the To and Bcc fields? Too impractical. I was hoping that someone was going to say "actually, you can get xp_readmail to return the resolved email address"...? But cheers anyhow
Yeah, it's an ASP forum. People can start a new thread either by filling in a form on the website - in which case the relevant fields are dropped into the database and CDONTS takes care of sending out notification emails - or by sending an email to one of the aforementioned accounts, in which case SQL Mail picks it up, processes it and sends out notification emails itself.
I find it hard to believe that xp_readmail won't return a resolved 'To' address at all - has anyone had any joy with this?
Is it the latter that is causing the problem ? How is the user submitting an email on your website - how are you taking the values they type into the page and submitting the email ? For each scenario, you have control over what is inserted into the db or what is being submitted as an email. In both cases, you can either insert a value into the db that tells you which forum it came from or use a hidden form variable in your asp page and use that in your email submission to tell which forum it was submitted from.
I think we might be at cross purposes It is the latter causing the problem - forum members submitting new posts via email, but they're not doing it from the website.
You can start a new thread by filling in a form on the site - with "title" and "body" fields - which gets dropped into the database. Then an email is sent to all the members of that forum detailing the new post. That's ok, that works. That's the ASP bit.
You can also start a new thread by sending an email - from Outlook, or whatever you're using - to a particular email address. SQLMail - running as an Exchange client, and all that palava, reads the email. This is where the problem lies.
Because there's two seperate forums we need to run here, and as SQLMail can only read one mail profile-thingy per server (and we've only got one server), both email addresses deliver to that same email box.
There's one database for each forum (both on the same SQL server installation) and each runs a stored proc as a scheduled job every 10 minutes. The stored proc (virtually identical in each database) looks at ALL the email in that one email box, and compares the 'From' address to the list of user's emails for whatever database it's running in. If there's a match, then the email is processed as a forum post.
Trouble is, there's a good chance that a user will be registered in both databases - the two forums deal with similar subjects. And under the method above, a stored proc has no way of telling which forum the email was intended for - except for the 'To' address. As explained, though, both the email addresses go to the same email box; the same Exchange profile, and xp_readmail returns the name of this profile - which is of course the same for each address - and not the resolved 'To' address.
We can't control what gets sent in the email - they simply type an address, a subject and a message and it gets fired off as a normal email. What I don't understand is why xp_readmail will return the resolved 'From' address, but not the 'To'.
Apologies for the long-winded post Any help much appreciated.