Running a query within a scheduled job

Question

I am looking at running the following query within a scheduled job weekly:

Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) 
        AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Differential'
       WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date
 GO

Not sure what I am doing wrong but I continue to get an error, I have tried running it from within the @query tag for the sendmail option.

Thomas Stringer · Answer 1 · 2013-05-16T15:19:17.497

As per the code in your comment:

USE msdb 

EXEC sp_send_dbmail 
    @profile_name = 'SQL Mail', 
    @recipients = 'vince.chan@ufa.com', 
    @subject = 'T-SQL Query Result', 
    @body = 'The result from SELECT is appended below.', 
    @query = '(include script from above)'

The reason this won't work is the same reason why this wouldn't work:

declare @query varchar(2048);

set @query = 'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date
 GO';

 exec (@query);

You're not taking into account the single quotes in the query string. Something like this should be what you need:

declare @query varchar(2048);

set @query = 'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date';

 exec (@query);

Therefore, your constructed query string should be:

'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date'

You need to use two single quotes in a string that contains a single quote.

score 1 · Answer 2 · answered May 16 '13 at 16:05

You can send HTML email using below code :

/*********************************************************************
Author      :   Kin
Date        :   5/16/2013
Tested RDBMS:   SQL Server 2005 and up .. for dba.stackexchange.com
Purpose     :   Send HTML Report
**********************************************************************/
declare @tableHTML nvarchar(max)
set @tableHTML =N'<H3><FONT SIZE="3" FACE="Tahoma">Backup report </FONT></H3>' 
set @tableHTML = @tableHTML +N'<table border="1" bgcolor=D7D1F8>' + -- change the background color if you want
           N'<FONT SIZE="2" FACE="Calibri">' +            
            N'<tr><th align="center">database_name</th>' +
            N'<th align="center">physical_device_name</th>' +
            N'<th align="center">TimeTaken</th>' +
            N'<th align="center">backup_start_date</th>' +
            N'<th align="center">BackupType</th>' +
            N'<th align="center">server_name</th>' +
            N'<th align="center">recovery_model</th>' +
            N'</tr>' +
           ISNULL(CAST ( ( 
            select TOP 10 
                    td = '',
                    td = s.database_name,'',
                    td = m.physical_device_name,'',
                    td = CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds','',
                    td = s.backup_start_date,'',
                    td = CASE s.[type]
                            WHEN 'D' THEN 'Full'
                            WHEN 'I' THEN 'Differential'
                            WHEN 'L' THEN 'Transaction Log'
                            END,'',
                    td = s.server_name,'',
                    td = s.recovery_model,''
                    FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date

    FOR XML PATH('tr'), TYPE 

            ) AS NVARCHAR(MAX) ),'') +
            N'</FONT>' +
            N'</table>' ;
-- send email 
EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'Profile_name', -- change here !!
            @recipients='sayhi@somecompany.com', -- change here !!
            @subject = 'Backup Report',
            @body = @tableHTML,
            @body_format = 'HTML' ;

Below is the output email :

enter image description here

Running a query within a scheduled job

2 Answers2

Linked