SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.
CREATE TABLE [dbo].[SMSCounter]( [Counter] [int] NOT NULL, [CounterDate] [datetime] NOT NULL )
380, 2012-09-10 00:00:00.000 177, 2012-10-14 00:00:00.000 999, 2012-11-16 00:00:00.000
The script below is my attempt to create a scheduled job that will check every hour if the
SMScounter.Counter is greater than 950.
USE DATABASEname select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0) IF CHECKVALUE > 950 BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients=Nfirstname.lastname@example.org', @body='1000 Daily SMS cap near', @subject ='1000 Daily SMS cap near', @profile_name ='SCOTT', @query = 'USE DATABASEname (select * from SMSCounter where CounterDate = GETDATE())' END
How can I format
dd/mm/yyand ignore the time part when reading
SMScounter.CounterDate? (script above edited , works now).
How can I get the
IFstatement to work to compare
I'm using SQL Server 2008 standard edition.