Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help

Author  Topic 

sstepter
Starting Member

3 Posts

Posted - 2014-12-11 : 19:14:58
I have a query, I will paste below. I would like to email the results of the query if there is a count > 1. Else, if no results are returned, do nothing. Please help, thanks!




Declare @DayThreshold int
Set @DayThreshold = 3

;With MaxFull as (
Select database_name, max(backup_set_id) backup_set_id
from msdb.dbo.backupset
Where type = 'D'
Group by database_name
)
SELECT
a.name AS Database_Name,
LEFT(CONVERT(varchar(16),a.create_date, 101),10) AS DB_Creation_Date,
LEFT(CONVERT(varchar(16),b.backup_start_date, 101),10) AS Backup_Start_Date,
LEFT(CONVERT(varchar(16),b.backup_finish_date, 101),10) AS Backup_Finish_Date,
mf.database_name
FROM master.sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b ON a.name= b.database_name
Left Join MaxFull MF on MF.database_name = a.name
WHERE a.name != 'tempdb'
AND ((backup_finish_date IS NULL AND a.create_date <= getdate()- @DayThreshold)
OR (backup_finish_date IS Not NULL AND backup_finish_date <= getdate()- @DayThreshold))
AND (MF.database_name is null or MF.backup_set_id = b.backup_set_id)
ORDER BY 4,1 desc;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-11 : 20:23:53
Count of what? What to do if the count = 1?



No amount of belief makes something a fact. -James Randi
Go to Top of Page

sstepter
Starting Member

3 Posts

Posted - 2014-12-12 : 11:03:46
So if 1 or more records is returned, Id like to execute sendmail if no results are returned, I want to do nothing and end query
Go to Top of Page
   

- Advertisement -