Query submission via email
-
Hi all,
Does anyone know how to submit a query in sql server 2k via email. I googled and searched this forum but couldn't find any topic.
Thanks,
Roshan
-
It is possible to configure SQL Mail for the server. Then set up a scheduled job that regularly uses sp_processmail to check for new mails and respond to them. I have not used this myself and would not recommend it since I do not see it as a job for the DBMS, so you will have to look it up in Books Online. Personally I would instead place that functionality outside SQL Server.
-
Chris,
Before posting to this forum I did went to the route you suggested but for some reason xp_sendmail gives me error. I googled and found that few other people have same issue and in every forum there was no answer suggested. Looks like there is something not integrating well with MAPI.
I was thinking of achieving this outside of SQL Server. We use webmethods for integrating our other applications. Webmethods does have this functionality for what I know.
If anyone has any other suggestions, please share it.
Thanks,
-
Try master.dbo.xp_smtp_sendmail.
XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.
It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds).
-
Well, xp_smtp_sendmail requires the extended stored procedure xp_smtp_sendmail to be installed.
It can be downloaded from http://www.sqldev.net/xp/xpsmtp.htm.
However, it can only be used to send mail, not read and respond to mail as was requested here.
-
If you have outlook installed on the SQL server there is a good sample article on this from the MSDN magazine (http://msdn.microsoft.com/msdnmag/issues/02/01/OutlookSQL/default.aspx). It may be possible to tweak the code and use SMTP mail if needed.
Good Luck
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply