August 20, 2013 at 4:06 pm
Hi Experts..
Requirement:
--If the following query resulted greater than zero rows, send the results in email in a tabular format using SQL Server database mail
--if is is equal to zero ,don't send the mail.
can someone help me in this
SELECT [DateTime]
,[Text]
FROM [MicrosoftLog].[dbo].[Log]
where [LogApplicationID] in (select [LogApplicationID]
from [MicrosoftLog].[dbo].[LogApplication]
where applicationname in
( 'VirtualMan',
'PhysicalMan')) and StartText<> 'count=0'
and StartDateTime < GetDate() and StartDateTime > dateadd(minute, -30, GetDate())
Any input is appreciated
Thanks
August 20, 2013 at 8:51 pm
DECLARE @Count INT
SELECT * FROM MyTABLE WHERE...;
SET @Count = @@ROWCOUNT;
IF @Count>0
BEGIN
-- call SendDBMail or whatever you want and e-mail the report.
END
August 21, 2013 at 12:11 am
SQListic (8/20/2013)
Hi Experts..Requirement:
--If the following query resulted greater than zero rows, send the results in email in a tabular format using SQL Server database mail
--if is is equal to zero ,don't send the mail.
can someone help me in this
SELECT [DateTime]
,[Text]
FROM [MicrosoftLog].[dbo].[Log]
where [LogApplicationID] in (select [LogApplicationID]
from [MicrosoftLog].[dbo].[LogApplication]
where applicationname in
( 'VirtualMan',
'PhysicalMan')) and StartText<> 'count=0'
and StartDateTime < GetDate() and StartDateTime > dateadd(minute, -30, GetDate())
Any input is appreciated
Thanks
Here is a technique where you only execute the actual query against your tables once, store the results in a global temp table, and only if there were results added to the temp table do you send email. This saves you from potentially having to run an expensive query against your system twice.
SELECT [DateTime],
[Text]
INTO ##MyGlobalTempTable
FROM [MicrosoftLog].[dbo].[Log]
WHERE [LogApplicationID] IN (SELECT [LogApplicationID]
FROM [MicrosoftLog].[dbo].[LogApplication]
WHERE applicationname IN ('VirtualMan', 'PhysicalMan'))
AND StartText <> 'count=0'
AND StartDateTime < GETDATE()
AND StartDateTime > DATEADD(minute, -30, GETDATE());
IF @@ROWCOUNT > 0
BEGIN
--send email with results
EXEC msdb.dbo.sp_send_dbmail
.
.
.
@query = N'SELECT * FROM ##MyGlobalTempTable;',
.
.
.
;
END
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply