none
How to concatenate field/column/variable to a string in the @body? RRS feed

  • Question

  • I have this T-SQL to email me if there are jobs that was scheduled but did not run for some reason.

    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP
    SELECT DISTINCT  J.NAME AS 'JOBNAME',  MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS 'RUNDATETIME'
    INTO #TEMP 
    FROM MSDB.DBO.SYSJOBS J  INNER JOIN MSDB.DBO.SYSJOBHISTORY H   ON J.JOB_ID = H.JOB_ID  WHERE J.ENABLED = 1  --ONLY ENABLED JOBS 
    ORDER BY JOBNAME, RUNDATETIME DESC
    
    IF NOT EXISTS (
        SELECT * FROM #TEMP
        WHERE JOBNAME = 'JOBNAME'
        AND RUNDATETIME = 'RUNDATETIME')
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default Account',
        @recipients = 'username@mysite.com',
        @body = JOBNAME, ' job did not run',
        @subject = 'The following object(s) was/were changed'
    END

    In the @body = I added "JOBNAME" and it's not working. I wanted to put the name of the job that didn't get executed in the body of the email.

    Tuesday, November 19, 2019 6:58 PM

All replies

  • I would create a cursor to use @JOBNAME variable and run your send mail inside the cursor with @JOBNAME as the @body text.
    Tuesday, November 19, 2019 7:16 PM
    Moderator
  • I have this T-SQL to email me if there are jobs that was scheduled but did not run for some reason.

    If the jobs are scheduled, they will surely run as per the scheduled and there is no need to have alert email like this.


    If the response helped, do "Mark as answer" or upvote it
    - Vaibhav

    Tuesday, November 19, 2019 7:20 PM
  • Hi Charlie2, 

    The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid. For more information , please check How to concatenate string when setting a parameter in Transact-SQL.

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 20, 2019 6:21 AM