1

I have a stored procedure which has 2 parameters @LastUpdateDate and @CurrentDate I calculate the difference between these two days and store it in another variable called Sub, like this

declare @sub int
select @sub = (datediff(dd,@LastUpdate,@CurrentDate))

If the value of Sub is greater than 0, I want to send an email notification to few people that the cube has not been refreshed since last day.

Can any one tel me how I can achieve this ? I tried sqldbmail, but that option didn't work

har07
  • 88,338
  • 12
  • 84
  • 137
user3063530
  • 79
  • 1
  • 2
  • 11
  • 2
    possible duplicate of [How to send email from SQL Server?](http://stackoverflow.com/questions/13300881/how-to-send-email-from-sql-server) – Leo Feb 18 '14 at 02:35
  • 1
    There are many many guides on using SQL Mail. Tell us specifically what you did and how it didn't work and we might be able to help further. – Nick.Mc Feb 18 '14 at 03:42

2 Answers2

0

I'm not sure about sqldbmail. The standard would be to use sp_send_dbmail.

It requires some set up which is discussed in the accepted answer to How to send email from SQL Server? as Leo commented.

Community
  • 1
  • 1
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
0

Personally I'm a fan of [ab]using SQL Agent for this - assuming that this is a scheduled check and I'm only contacting SQL-admin-bods...

I would set up a job with a single T-SQL step with code along the lines of:

EXEC your_sproc 'etc', 'etc';

IF DateDiff(dd, @LastUpdate, @CurrentDate) > 0
  BEGIN
    RAISERROR ('Cube not refreshed', 18, 1);
  END
;

Then I would set a notification email when the job fails

enter image description here

gvee
  • 16,732
  • 35
  • 50