Envio de e-mail através de SP

Question

É possível realizar o envio de e-mails de dentro de uma Stored Procedure? Há algum recurso que permita isto?

Answer 1

sem problemas se vc. estiver usando o sql 2000 vc. vai precisar ter um exchange e o outlook instalado no sevirdor onde esta o sql server assim vc. cria um profiler mapi para a conta do usuario que sobe o servico do sql server e entao pode usar a proc xp_sendmail.

se estiver usando o sql 2005 vc. pode configurar o dbmail, ele nao precisa do exchange e nem do outlook vc. pode usar qualquer servidor de email smpt

 

vc. tambem pode usar a funcao xp_smtp_sendmail que pode ser instalada no seu servidor, mais essa funcao e de 3 e vc. tambem precisa ter um mapt profle do outollok no servidor

 

veja qualquer coisa retorne.

 

Abs;

Answer 2

TEM UM JEITO MELHOR DO QUE ESTE

SEGUE PROCEDURE ABAIXO

Só precisa configurar na procedure

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.SERVIDOR.com.br'

-- Replace 25 by the PORT SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")', '25'

 

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SendMail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SendMail]
GO

CREATE PROCEDURE [dbo].[sp_SendMail]
 @From varchar(100) ,
 @To varchar(100) ,
 @Subject varchar(100)=" ",
 @Body varchar(8000) =" "
AS
/******************************************
This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/ library/en-us/cdosys/html/_cdosys_messaging.asp
*******************************************/

Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--***** Create the CDO.Message Object *****
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.SERVIDOR.com.br'

-- Replace 25 by the PORT SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")', '25'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
 IF @hr <>0
     BEGIN
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
         BEGIN
           SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
         END
       ELSE
         BEGIN
           PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
         END
     END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

 

Answer 3

Juliano,

Eu recomendária a utilização da sp_send_dbmail no SQL Server 2005, pois a xp_sendmail, será descontinuada nas próxima versões do SQL Server.

Além disso, a sp_send_dbmail, não precisa trabalhar com profiles MAPI para enviar ou receber e-mail's.

Answer 4

Concordo com o Junior