March 26, 2009 at 7:04 am
I am trying to send the results of this very useful query that I believe I found on this site, the query works fine by itself but when trying to send this off through and email i am getting the following error.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'NEVER'.
EXEC master.dbo.xp_sendmail
@recipients = N'nyob@nyob.com',
@query = N'SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, LastBackupDate desc, a.type',
@subject = N'SQL Server Report',
@message = N'Database Backup Activity:',
@attach_results = 'TRUE',
@width = 250 ;
Any suggestions would be very appreciated,
March 26, 2009 at 8:19 am
I figured out what I was doing wrong, Solution...
Double up on single quotes:
@query = N'SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), ''NEVER'') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), ''NEVER'') as LastBackupDate,
case
when type= ''D'' then ''** FULL **''
when type= ''I'' then ''DIFFERENTIAL''
when type= ''L'' then ''LOG''
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, LastBackupDate desc, a.type'
March 27, 2009 at 12:52 am
Heh gooders those quotes are always annoying but instead of using xp_sendmail (SQL Mail); I would recommend you use sp_send_dbmail (Database Mail). It is more secure and does not require MAPI setup on the server as it uses SMTP.
Also xp_sendmail will be phased out in future releases...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 27, 2009 at 10:27 am
For those that may want to see it, I fought my way though and did what Mohit suggested and created a 2005 'sp_send_dbmail' solution as well if anyone would like to use them.
Send results as a attachment:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Backup Alert',
@recipients = 'nyob@nyob.com',
@query = 'SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), ''NEVER'') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), ''NEVER'') as LastBackupDate,
case
when type= ''D'' then ''** FULL **''
when type= ''I'' then ''DIFFERENTIAL''
when type= ''L'' then ''LOG''
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, LastBackupDate desc, a.type' ,
@subject = 'SQL Server Report ***TEST***',
@attach_query_result_as_file = 1 ;
And send the query results as HTML:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N' H1>Database Backup Report /H1>' +
N' table border="1">' +
N' tr> th>Database /th> th>Days since Backup /th>' +
N' th>Date /th> th>Backup Type /th>' +
CAST ((SELECT td = B.name, '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td = ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER'), '',
case
when type = 'D' then '** FULL **'
when type = 'I' then 'DIFFERENTIAL'
when type = 'L' then 'LOG'
else 'N/A'
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, a.type
FOR XML PATH ('tr'), TYPE)AS NVARCHAR(MAX))
+ N' /table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='nyob@nyob.com',
@profile_name = 'Backup Alert',
@subject = 'Database Backup Report',
@body = @tableHTML,
@body_format = 'HTML' ;
****Please make special note that in the HTML solution I chose to remove all the opening brackets because this web page was making the elements disappear. Please if you use this code go through and put the open brackets back before running it.****
March 27, 2009 at 11:24 am
Thanks for sharing :).
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply