Adding Report to sp_send_cdosysmail | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Adding Report to sp_send_cdosysmail

If I run the following query I get a lsit of all databases on my server, and the last time they had a full backup: SELECT LEFT(B.name,20) as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), ‘NEVER’) as DaysSinceLastBackup,ISNULL(Convert(char(10),
MAX(backup_finish_date), 101), ‘NEVER’) as LastBackupDate
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = ‘D’
GROUP BY B.Name That works fine. What I want to do is add the output to this query as the body of an email using the sp_send_cdosysmail procedure published elsewhere on this site. My complete syntax is: declare @body nvarchar(4000) set @body =(SELECT LEFT(B.name,20) as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), ‘NEVER’) as DaysSinceLastBackup,ISNULL(Convert(char(10),
MAX(backup_finish_date), 101), ‘NEVER’) as LastBackupDate
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = ‘D’
GROUP BY B.Name )
exec sp_send_cdosysmail ‘[email protected]‘,’[email protected]‘,’Backup Status’,@body Unfortunaltly, when I run this, I get the following: Server: Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help is appreciated.
Hi, You can’t set multiple columns or rows to a single variable. Since @body is an varchar. It may have sclar value only. Try to retrun your query out put as single text, then it won’t be give any error. Thankx.. -Saravanan.
or You can concatenate the multiple columns into single column Select @body =LEFT(B.name,20) +
cast(ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), ‘NEVER’) as varchar(20))+ cast(DaysSinceLastBackup as varchar)+
cast(ISNULL(Convert(char(10), MAX(backup_finish_date), 101), ‘NEVER’) as varchar(20))
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = ‘D’
GROUP BY B.Name exec sp_send_cdosysmail ‘[email protected]‘,’[email protected]‘,’Backup Status’,@body
Madhivanan Failing to plan is Planning to fail
]]>