How to place DB Server Name in Email Subject Line ??

• Jeff-67583

  SSCertifiable

  Points: 5625

  More actions

  June 16, 2005 at 8:56 am

  #87759

  Hello,

  Using MS Outlook/MSSQL2000. I'm trying to create a message using xp_sendmail similar to MSSQL backup/DTS/Alert notification emails sent to operators:

  Subject: SQL Server Job System: 'DTS_SAPJOB2' completed on \\Server01

  How do I get the part "\\Server01" in the subject line? I tried //%s. I also tried @@SERVERNAME, which completes query but doesn't send an email.

  Here's the current code:

  Declare

   @RCPT VARCHAR(500),

   @MSG VARCHAR(400)

  Set @MSG = 'Jeff Was Here'

  Set @RCPT = 'flintstone@bedrock.ca'

  DECLARE @EMAIL VARCHAR(600)

    SET @EMAIL = 'EXEC master.dbo.xp_sendmail

      @recipients = ''' + @RCPT + ''',

      @message = ''' + @MSG + ''',

      @subject = ''!! LOW FREE DISK SPACE ON ' + '\\%s'  + ' !!'''

      -- @subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''

    EXEC (@EMAIL)

   

  Many thanks. Jeff

• Mike Metcalf

  SSCrazy Eights

  Points: 8763

  More actions

  June 16, 2005 at 10:04 am

  #566176

  Declare

   @RCPT VARCHAR(500),

   @MSG VARCHAR(400), @SBJCT VARCHAR(100)

  select @SBJCT = '! LOW FREE DISK SPACE ON ' + @@Servername

  DECLARE @EMAIL VARCHAR(600)

  SET @EMAIL = 'EXEC master.dbo.xp_sendmail

      @recipients = ''' + @RCPT + ''',

      @message = ''' + @MSG + ''',

      @subject = ''!! LOW FREE DISK SPACE ON ' + '\\%s'  + ' !!'''

      -- @subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''

    EXEC (@EMAIL)

  ------------------

  I would also ask why you're doing it this way, you can just pass the variables into the XP_Sendmal procedure without it.

  Your choice though