Thanks for the reply.
I am presently trying this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [No] AS 'td','',[date] AS 'td','',
[DAYS] AS 'td','', Variance AS 'td'
FROM TestTbl
WHERE(Variance >= @Start AND Variance < @End)
AND CAST(CONVERT(VARCHAR,curDate,106) AS DATETIME) = CAST(@Date AS DATETIME)
ORDER BY No
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>test Report</H3>
<table border = 1>
<tr>
<th> No </th> <th> date </th> <th> DAYS </th> <th> Variance </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alert', -- replace with the SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'abc@gmail.com', -- multiple users specify ;
@subject = 'Test Report';
END
from the front end I am passing values for the range in variance and date.
This will fetch values in specific range and based on it email will be sent to the users
Now I want to set alerts, in the front end I want to specify a range of values for this report.So the values entered in .NET interface will be stored in the database table and then I would want the database table to be checked at specific time intervals and if the variance range is met an email will be triggerred to the users.The database table will be updated with data every hour and I want to check for the range every hour and if the variance range is met hence email is to be triggered.
How can this be achieved can you please help with a sample code that does all this?
Hope I am clear with the requirement.Kindly let know if any further clarity is required.
Thanks in Advance for helping me out
