sp_send_mail question

• washawgolla

  SSC Eights!

  Points: 841

  More actions

  March 20, 2009 at 12:37 pm

  #128819

  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: 143329

  More actions

  March 20, 2009 at 5:19 pm

  #963857

  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

  More actions

  March 28, 2019 at 4:15 am

  #2034066

  I have this store procedure:

  go
DECLARE @Message VARCHAR(7000), @test-2 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-2 = 'Valideringsstatus på Organisation Afdelinger: '--set @Message = 'Status på validering af Afdelinger:'
--SET @test-2 = 'Valideringsstatus på Organisation Afdelinger: '
--set @Message = 'Status på validering af Afdelinger:'

  SET @test-2 = '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-2 =
'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-2, @body = @MessageEXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'xx', @recipients = 'xx''xx', @subject = @test-2, @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.