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.



  • 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.


  • 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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply