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

  • السؤال

  • 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.

    22/ربيع الأول/1441 06:58 م

جميع الردود