Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

PROBLEM

The 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.

TABLE CREATE

CREATE TABLE [dbo].[SMSCounter](
  [Counter] [int] NOT NULL,
  [CounterDate] [datetime] NOT NULL )

EXAMPLE DATA

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

JOB SCRIPT

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=N'scott@mydomain.com',
  @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

QUESTIONS

  1. How can I format GETDATE to examine dd/mm/yy and ignore the time part when reading SMScounter.CounterDate? (script above edited , works now).

  2. How can I get the IF statement to work to compare CHECKVALUE to 950?

I'm using SQL Server 2008 standard edition.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

You can use convert (date, GETDATE()) to get just the 'date' part:


select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:


declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:


USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.

share|improve this answer
    
Works a treat thank you Stuart. –  scott_lotus Nov 16 '12 at 9:40
add comment

The solution is not bad, but I would like to introduce you to a more real time one. Instead of just checking the SMS counter every hour, wouldn't you better know when it first happened?

I would do the following:

  • add a new column to your table - Alert bit not null default 0 - which will hold info if the customer was alerted about the high number of SMS. This is needed so that the customer isn't alerted for every insert/update > 950, but only for the first one.

  • add a trigger on your table (after insert and update) that in case the value for the Counter is bigger than 950 will start the alert (set the alert column to 1 and send email).

The trigger would look like the following:

ALTER TRIGGER dbo.RaiseAlert
    ON  dbo.SMSCounter
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    declare
        @cnt int,
        @today DATE = cast(GETDATE() as DATE),
        @alert bit;

    select @cnt = n.[Counter], @alert = alert
    from inserted n
    where n.CounterDate = @today;

    if @cnt > 950 and @alert = 0
    begin
        update SMSCounter
        set Alert = 1
        from SMSCounter
        where CounterDate = @today;

        declare @subject varchar(1000);
        select @subject = 'The sms counter is near!! It''s value is: ' + CAST(@cnt as varchar);

        EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLAlerts',
            @recipients = 'xxx@xxx.com',
            @subject = @subject,
            @body = 'blablabla';
    end;
END
GO

A simple update would raise it and send the email:

update SMSCounter
set [Counter] = 988
where CounterDate = '2012-11-16 00:00:00.000'
share|improve this answer
    
Excellent , thank you for the post. Traditionally steered away from triggers at our developers request but i can see how useful that would be. Will have a play. –  scott_lotus Nov 16 '12 at 11:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.