Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The query by itself is just fine. Can you post the actual scripted command you are using to create the job as well as the mail send command? My guess is you're having quoting issues. –  Thomas Stringer May 15 '13 at 17:47
    
My theory behind this entire query was to email me the results, but when I run the entire query with the email portion I get the following error: Msg 102, Level 15, State 1, Line 10 Incorrect syntax near ' + '. below is the entire query I edited: USE msdb EXEC sp_send_dbmail (at)profile_name = 'SQL Mail', (at)recipients = 'vince.chan@ufa.com', (at)subject = 'T-SQL Query Result', (at)body = 'The result from SELECT is appended below.', (at)query = ' (include script from above)' –  Vince May 15 '13 at 18:34
    
My recomendatin would be to create a stored procedure that does this and call the stored procedure inside your scheduled job –  Cougar9000 May 15 '13 at 18:36
    
@Courgar9000, thanks for the suggestion, threw the query into a stored procedure. One question I have is, the output that I am getting in my email is a littler hard to read, what are some ways I can have it properly formatted, maybe even into a table of some sort? thanks –  Vince May 15 '13 at 19:15
add comment

2 Answers

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.

share|improve this answer
    
Thanks Thomas, I threw that into my query and it worked. One question I do have is, how can I properly format the email so that the output isn't so hard to read, am I able to output the query so that the information is written into a table or even csv? –  Vince May 15 '13 at 19:26
1  
@Vince There's actually a pretty good example of this on TechNet. See this link all the way at the bottom titled Sending an HTML e-mail message (technet.microsoft.com/en-us/library/ms190307.aspx). –  Thomas Stringer May 15 '13 at 19:37
add comment

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

share|improve this answer
    
To throw a wrench into this, if I wanted to run this query against multiple servers from a single location, what's the best method to achieve this? I have a few SQL servers I would like to pull this information for and complie into a single email, versus having multiple emails from each server. –  Vince May 22 '13 at 22:08
    
You can use SQL cmd or Powershell to connect to different servers and dump the data into a single table and then generate report out of it. –  Kin May 22 '13 at 22:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.