Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: adding date timestamp to xp_sendmail procedure

    I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:

    use master;
    go

    CREATE PROC pr_sendmail
    AS

    DECLARE @DT DATETIME
    SET @DT=GETDATE()

    BEGIN

    EXEC xp_sendmail @recipients = 'me@work.com',
    @message = 'send email from SQL Server Stored Procedure.',
    @copy_recipients = 'me@work.com',
    @subject = 'Job Started at ', @DT

    END

    How do I get this to work? Thanks!

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    @subject = 'Job Started at ' + cast(getdate() as varchar)

    you can also use CONVERT instead of CAST to format the date in various different formats.

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    I tried this : @subject = 'Job Started at ' + cast(getdate() as varchar)
    and get and error 170: incorrect syntax near '+'.
    I want to automate a daily email showing the date of yesterday on the subject line. The message is updated daily but I have to enter the date manually. Please help. Thanks!!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Stored procedures can only take literal constants, or variables as arguments. not expressions.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First off, it is a bad practice to create anything in the master database. Avoid bad mojo, do this somewhere else if you must do it.

    Next order of business, xp_sendmail is deprecated. Use Database Mail instead.

    If you are still convinced that you want to hurt yourself with two kinds of bad behavior, then you can do that using:

    NOTE: This is two flavors of bad, it will hurt you!
    Code:
    use master;
     go
    
     CREATE PROC pr_sendmail 
     AS
    
     DECLARE @DT VARCHAR(255) = 'Job Started at ' + Convert(VARCHAR(255), GetDate(), 121)
    
     BEGIN 
    
     EXEC xp_sendmail @recipients = 'me@work.com', 
     @message = 'send email from SQL Server Stored Procedure.', 
     @copy_recipients = 'me@work.com', 
     @subject = @DT
    
     END
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2013
    Posts
    3
    Below is the original code I have, probably not created by an expert. I add your code and get error 139: cannot assign a defulat value to a local variable.

    CREATE PROCEDURE sp_email_test
    AS
    SET NOCOUNT ON
    exec master..xp_sendmail 'me@work.com',
    'exec sql,
    @subject = 'Job done on'
    ,@width = 255
    exec xp_stopmail
    GO

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yikes! You are running a very old version of SQL Server. The following code should run at least as far back as SQL 7.
    Code:
    CREATE PROC pr_sendmail 
    AS
    BEGIN 
       DECLARE @DT VARCHAR(255) 
       SELECT @DT = 'Job Started at ' + Convert(VARCHAR(255), GetDate(), 121)
    
        EXEC xp_sendmail @recipients = 'me@work.com'
    ,     @message = 'send email from SQL Server Stored Procedure.'
    ,     @copy_recipients = 'me@work.com'
    ,     @subject = @DT
    
    END
    I'm just being nosey now, but what version of SQL are you using???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Sep 2013
    Posts
    3
    MS SQL Server 8.0. Your code works. Let me start with yours and see if it works.
    Last edited by debut; 09-12-13 at 17:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •