sp_send_mail question

  • washawgolla

    Say Hey Kid

    Points: 701

    I am trying to convert all our main application to use sp_send_db_mail; here is my question though;

    One profile name is defined in the server for all application to use to send their email, but this cause some conflict of point of interest;

    Some groups want to append Alias to the from address of their own; say for example

    if the profile name defined to return its from address

    Application-Alert[abc@usbc.com]

    group1 wants it to be like Group1 Application Alert[abc@usbc.com]

    Group2 wants it to be like Group2 Application Alert[abc@usbc.com]

    can we fulfill with one profiler name defined, please forward your suggestions

    Thanks

  • RBarryYoung

    SSC Guru

    Points: 143327

    You can certainly do this with DBMail.

    Plus, I would really think that you would want to use DBMail on SQL 2005 anyway.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • reza.azimi

    SSC Rookie

    Points: 49

    I have this store procedure:

    go
    DECLARE @Message VARCHAR(7000), @test VARCHAR(8000), @status varchar(50), @antal varchar(50), @support varchar(6000), @Hilsen varchar(500), @introduktion varchar(2000), @CRLF char(10), @servername varchar(500)

    select @status = (isnull(cast(count(*) as varchar), 0) + ' ' + isnull(@status, ' ') ) select @status = (isnull(cast(count(*) as varchar), 0) + ' ' + isnull(@status, ' ') )

    select @status=ValidationStatus, @antal= cast(count(validationstatus) as varchar) + ' ud af ' + cast((select count(*) from [mdm].[Afdelingsnr]) as varchar) from [mdm].[Afdelingsnr]where ValidationStatus= ('New, Awaiting Validation')group by ValidationStatus
    select @status=ValidationStatus,
    @antal= cast(count(validationstatus) as varchar) + ' ud af ' + cast((select count(*) from [mdm].[Afdelingsnr]) as varchar)
    from [mdm].[Afdelingsnr]
    where ValidationStatus= ('New, Awaiting Validation')
    group by ValidationStatus

    --SET @test = 'Valideringsstatus på Organisation Afdelinger: '--set @Message = 'Status på validering af Afdelinger:'
    --SET @test = 'Valideringsstatus på Organisation Afdelinger: '
    --set @Message = 'Status på validering af Afdelinger:'

    SET @test = 'Valideringsstatus på Afdelinger'set @introduktion = 'Som dataansvarlig for DEASMDM Afdelinger får du hermed en valideringsstatus.'set @Message = 'Status på validering af Afdelinger: ' set @support ='Venligst kontakt DEAS BI på for yderligere spørgsmål. 'set @Hilsen = 'med venlig hilsen         DEAS BI.'set @CRLF = char(10) + char(13)SET @test =
    'Valideringsstatus på Afdelinger'
    set @introduktion =
    'Som dataansvarlig for DEASMDM Afdelinger får du hermed en valideringsstatus.'
    set @Message =
    'Status på validering af Afdelinger: '
    set @support =
    'Venligst kontakt DEAS BI på xxxx for yderligere spørgsmål. '
    set @Hilsen = 'med venlig hilsen
            DEAS BI.'
    set @CRLF = char(10) + char(13)

    SET @Message = 'Hej' +  @CRLF + @introduktion + ' ' + @CRLF + @message + ' ' + cast(@antal as varchar) + ' ' + 'der afventer validering/opdatering' + '.' +  ' '  + @CRLF +  'Venligst valider de(n) nye afdeling(er) i MDS i listen XXX_Afdelingsnr'+ '.' +  ' '  + @CRLF +  @support  + ' ' +  @CRLF +  @Hilsen --SET @Message = 'Status på validering af Afdelinger'SET @Message = 'Hej' +
     @CRLF +
     @introduktion +
     ' ' +
     @CRLF +
     @message +
     ' ' +
     cast(@antal as varchar) + ' ' + 'der afventer validering/opdatering' + '.' +
      ' '  +
     @CRLF +
     'Venligst valider de(n) nye afdeling(er) i MDS i listen XXX_Afdelingsnr'+ '.' +
      ' '  +
     @CRLF +
     @support  +
     ' ' +
     @CRLF +
     @Hilsen--SET @Message = 'Status på validering af Afdelinger'

    EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'xx', @recipients = , @subject = @test, @body = @MessageEXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'xx', @recipients = 'xx''xx', @subject = @test, @body = @Message

    but my problem is that profile name and recipients are hardcoded and hence when I want to execute this on another server then I need to change the profile name one by one. So my question is how can I change this sql script so the profile name as well as recipient are dynamic and works across all servers.

Viewing 3 posts - 1 through 3 (of 3 total)

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