August 5, 2011 at 11:41 am
I want to have SQL read new messages from a specific mailbox and mark them read. Anyone ever done something like this?
August 5, 2011 at 11:51 am
not possible from SQL. doesn't matter if you are talking exchange or an SMTP/POP3 service, there's no built in way to READ emails back.(sp_send_dbmail lets you SEND an email)
you'd have to do this in a programming language, like in a CLR, adn maybe you could have SQL call that program form xp_cmdshell or CLR.
it would be better to have a program running and inserting emails into a table, so then it is available for SQL to use, isntead of trying to actually read the mail from TSQL, even indirectly like i described above.
in this case TSQL is the wrong tool for the job.
Lowell
August 5, 2011 at 11:55 am
Do you know what could write to a table?
August 5, 2011 at 12:03 pm
checkai (8/5/2011)
Do you know what could write to a table?
sure...but what is the question?
you could build a string in the application and do ExecuteNonQuery("insert....")
or you could use a datatable and call UpdateDataTable, so it would update it's boulnd SQL table with the nsert/update/deletes.....
trying to think outside of the box, why do you think you need to read a mailboxes messages into a table? what are you trying to do? if you had a message in a row in a table, what would you do with it?
Lowell
August 8, 2011 at 2:08 pm
I want people in the field to be able to email a mail generic account with a client's ID number as the subject. Then have SQL generate a dataset and email them back with client data to their mobile device.
August 8, 2011 at 2:15 pm
ok...but why via email? why not just have them go to a web page that generates a dataset, and presents the results, based on data input of those people in the field?
if they are in the field and have email access, then they have internet access too, right? then the page can present them witha save to excel or save to pdf as another option..
isn't that easier?
otherwise, if you were doing this in say, vb.net or c#.net, if you get an email, do you have to parse the subject or the body to filter the dataset results?
there's a lot you haven't told us here...it's possible in a programmming language, I can point you towards using POP3 to read email...but the details on what to do with a specific email are pretty fuzzy, other than generate a dataset/sql statement.
Lowell
August 8, 2011 at 2:34 pm
Plan L might be to call an SP which generates html and then sends via dm mail. It would not be my first choice but this would work.
August 17, 2011 at 4:27 am
You could use a script task in SSIS and bind to a job that runs every 5 minutes.
August 17, 2011 at 4:45 am
checkai (8/8/2011)
I want people in the field to be able to email a mail generic account with a client's ID number as the subject. Then have SQL generate a dataset and email them back with client data to their mobile device.
Email? Why not fax? Or, better, if your people are in the real "fields", pigeon mail - fast and stylish - no one have such service - yours one will be the winner :hehe:
Seriously, why not simple standard Mobile App: user enters clientId and gets back all required details straight away.
If you end up using email for what you are trying to do, make sure it's encrypted otherwise you may have to see some legal implecations. By the way, in my humble opinion, such design would look like total crap...
May 24, 2012 at 9:12 pm
I am looking for something similar, here's the closest i've found.
http://www.sqlteam.com/article/sqlmail-in-depth
yes, it is possible, and built-in with something called SQL Mail (as opposed to "database mail").
i havent been able to set it up myself, getting an error that it doesnt support 64 bit. but if that is not an issue for you, this would be the simplest way, all the coding done in SQL language. all other approaches i found so far involve other programming languages.
the requirement is very valid, some people may not get it. in my case, as an alternative simplified way for salesreps to submit orders while on the filed, considering bad AT&T connectivity that sometimes prevents our fancy order entry web app to function at 100%. besides, nothing easier, faster and more natural for some users than to provide information through email, don't you think?
good luck to ya
xp_readmail
@msg_id = 'message handle to read',
@peek = '{true|false}',
@date_received = 'message date' OUTPUT,
@originator = 'sender's friendly name' OUTPUT,
@originator_address = 'sender's resolved address' OUTPUT,
@subject = 'message subject' OUTPUT,
@message = 'message body' OUTPUT,
@recipients = 'recipient list' OUTPUT,
@cc_list = 'carbon copy recipient list' OUTPUT,
@bcc_list = 'blind copy recipient list' OUTPUT,
@unread = '{true|false}' OUTPUT,
@suppress_attach = '{true|false}',
@attachments = 'attachment list' OUTPUT
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply