Query submission via email

Question

Query submission via email

Roshan Patel

SSC Veteran

Points: 236
More actions
May 18, 2005 at 8:25 pm

#164403

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

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 Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Roshan Patel SSC Veteran Points: 236 More actions · Answer 2

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,

deucellion Say Hey Kid Points: 680 More actions · Answer 3

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

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.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

nclarke SSC Enthusiast Points: 165 More actions · Answer 5

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