none
Envio de e-mail através de SP RRS feed

Todas as Respostas

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

    quarta-feira, 28 de fevereiro de 2007 09:55
  • 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

     

    quarta-feira, 28 de fevereiro de 2007 11:56
  • 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.

    quarta-feira, 28 de fevereiro de 2007 12:05
  • Concordo com o Junior
    quarta-feira, 28 de fevereiro de 2007 13:30