Send Email every 30 minutes if Insert Happens???

Question

Send Email every 30 minutes if Insert Happens???

obarahmeh

SSCommitted

Points: 1595
More actions
July 6, 2008 at 2:28 am

#124667

Dear All,
I need to do the following:
I need to send an Email every 30 minutes is Insert happenned on 'myTable'...
I just do not know where to go to implement that.
I think this can be done by creating Job and schedule it, if this is right, I've created a job, but I could not create a step to check if an Insert happenned then fire an alert to send an Email.
Plz help to accomplish this issue... I think this is just like the one in this web site (when you create a job alert - when you go to SQL Jobs).
Summery: How to send an Email every 30 minutes or every day if Insert operation has been done on "myTable"???
OBarahmeh

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Well, the first thing you need to do is identify how you will determine whether or not your table has a new row (inserted).

Do you have any date columns on this table? Is one of them a created date for the row? Can you verify that the information in that column is only modified when a new row has been created?

If so, then you could do something like:

If Exists (Select * From MyTable Where datecolumn >= dateadd(minute, -30, getdate()))

Begin;

-- code to send mail here

End;

If you don't have any date columns to identify this - then you might be able to do something with an identity column if you have one on your table. This would involve something like:

Declare @seed int;

Set @seed = (Select seed From LastSeedTable);

If Exists (Select * From MyTable Where identityColumn > @seed)

Begin;

Update LastSeedTable

Set seed = (Select max(identityColumn) From MyTable);

-- code to send email

End;

You can then put the above in a stored procedure and call it from a SQL Agent job - or, just put the code in the Agent job.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 2

how about creating a trigger on 'myTable' for inserts, and creating a separate 'audit' table

When a new row is inserted into 'myTable', insert a row into the audit table with the date/time of the insert and any other values needed -

then set up the scheduled job to run every 30mins and check the dates in the audit table, sending an email if one or more of the rows was inserted in the previous 30mins

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Ivanna Noh (7/7/2008)
how about creating a trigger on 'myTable' for inserts, and creating a separate 'audit' table
When a new row is inserted into 'myTable', insert a row into the audit table with the date/time of the insert and any other values needed -
then set up the scheduled job to run every 30mins and check the dates in the audit table, sending an email if one or more of the rows was inserted in the previous 30mins

If there are no date columns or identity (or other monotonically increasing value) on the table, then yes - this would be another option.

I wouldn't implement a trigger just for this purpose though, especially if all it takes is a simple query to identify the new rows.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 4

I wouldn't implement a trigger just for this purpose though, especially if all it takes is a simple query to identify the new rows.

Agreed, triggers are a bit of a last resort! However, the OP hasn't made it clear if there is a column in myTable that could be used to identify when the insert occurred 🙂

obarahmeh SSCommitted Points: 1595 More actions · Answer 5

Thanks for ur Comments and reply,

First: I have two columns; one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.

And I get these two dates when the rows are inserted into the table. So I used the first option to get the last rows in the last 30 minutes, which is by:

What I did is: I've created a job with one step containing the following code to check the new rows and then send email, and schedule this job to run every 30 minutes.

If Exists (Select * From MyTable Where DayTransTime >= dateadd(minute, -30, getdate()))

Begin;

code to send email

End;

I tried the previous code and email is sent successfully, BUT this code is always sending Email every 30 minutes and does not behave that it checks if new rows had been inserted in the last 30 minutes.

I don't know if you gues got the problem or not, but I tried it and email is sent every 30 minutes wheather there are new rows or not.

Note # 1: I don't wanna send Email if there is no new rows inserted in the last 30 minutes.

Note # 2 (important): I passed the body of the Email manually (normal text), but I need to send the new rows inserted in the last 30 minutes...Is this possible???

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

obarahmeh (7/9/2008)
Thanks for ur Comments and reply,
First: I have two columns; one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.

Dates should be stored in the database as datetime data type.

You are going to have to convert the decimal date and time into a valid datetime column for the comparison. I think this might do it:

SELECT columns

FROM table

WHERE convert(datetime, cast(DayTransDate AS char(8))

+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6), 5, 0, ':'), 3, 0, ':'))

> DATEADD(minute, -30, getdate());

As for sending the rows from the query, I don't know what you are using to send the mail. In SQL Server 2005 - you could use sp_send_dbmail to send the above query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

obarahmeh SSCommitted Points: 1595 More actions · Answer 7

Dear Jeffrey,

My application needs to store the dates as Decimal for some requirement, and your opinion to convert the decimal into date is right, and I was trying to write the query for this conversion.

Any way, I tried your query, but there is an error that I could not figure it out...this is because I did not use the STUFF function before and have no idea how it works.

please try to use your query in your SQL server 2005 and check if every thing works well or not.

This is your query with one ")" I think you missed:

SELECT * FROM MyTable

WHERE convert(datetime, cast(DayTransDate AS char(8))

+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6), 5, 0, ':'), 3, 0, ':')))

> DATEADD(minute, -30, getdate());

With my best Regards to you...

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

To actually send an email notification via SMTP, WITHOUT requiring that Outlook be running on the host machine of SS2K and WITHOUT having to configure an email account in SS2K, the code has already been posted at this URL:

[font="Courier New"]http://www.sqlservercentral.com/Forums/Topic527864-110-1.aspx[/font]

Have fun

J-440512 SSCertifiable Points: 6285 More actions · Answer 9

Regarding the use of Triggers:

The one reason I avoid triggers like the plague is because these are unseen, i.e. if a stored procedure (for instance) is found to be causing trouble down the road, it is too easy to forget checking - or too cumbersome -- whether or not each of the tables used by the stored procedure has a trigger.

Any further input on why triggers should be avoided ?

Regards

obarahmeh SSCommitted Points: 1595 More actions · Answer 10

Deal All,

Thanks for all ur replies.

I found out the error cause, it is not an error in the query. I suddenly found some values in the column DayTransTime (Decimal(6,0)) that its length is 5 (not 6), so an error appear in this case. I think I need now to convert for both cases of length 6 and length 5.

I've created a new query with some help, and my new query now is:

SELECT DayTransDate* 1000000 + DayTransTime,

convert(datetime, cast(DayTransDate AS char(8)) + ' ' +

STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')),

CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +

REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', ''),

REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')

FROM MyTable

WHERE DayTransDate* 1000000 + DayTransTime

< REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')

and len(DayTransTime)=6;

The question now: How can I retrieve the rows that their DayTransTime length is not 6???

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

obarahmeh (7/13/2008)
Deal All,
Thanks for all ur replies.
I found out the error cause, it is not an error in the query. I suddenly found some values in the column DayTransTime (Decimal(6,0)) that its length is 5 (not 6), so an error appear in this case. I think I need now to convert for both cases of length 6 and length 5.
I've created a new query with some help, and my new query now is:
SELECT DayTransDate* 1000000 + DayTransTime,
convert(datetime, cast(DayTransDate AS char(8)) + ' ' +
STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')),
CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +
REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', ''),
REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')
FROM MyTable
WHERE DayTransDate* 1000000 + DayTransTime
< REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120), '-', '') , ':', '') , ' ', '')
and len(DayTransTime)=6;
The question now: How can I retrieve the rows that their DayTransTime length is not 6???

Hi, I posted a solution on Friday - but I guess I took too long writing it up and it was lost. The original solution I posted had a few mistakes - guess that's what I get for writing air code. The problem is converting the decimal values to a valid datetime to compare to 30 minutes ago, and my original stuff statements work - but only if there are guaranteed to be 6 characters in the time.

The following works and will be a lot simpler than what you have above:

Declare @MyDate datetime;

Set @MyDate = '2008-07-13 09:40:00'; --Change this to get different results

Declare @MyTable Table (DayTransDate decimal(8,0), DayTransTime decimal(6,0));

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 090000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 090500);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 091000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 092000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 093000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 100000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 100500);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 101000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 102000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 103000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 104000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 105000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 105500);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 110000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 111000);

Insert Into @MyTable (DayTransDate, DayTransTime) Values(20080713, 112000);

Select DayTransDate

,DayTransTime

,cast(DayTransDate As char(8))

,stuff(stuff(DayTransTime, len(DayTransTime) - 1, 0, ':'), len(DayTransTime) - 3, 0, ':')

From @MyTable

Where cast(DayTransDate As char(8)) + ' '

+ stuff(stuff(DayTransTime, len(DayTransTime) - 1, 0, ':'), len(DayTransTime) - 3, 0, ':')

> dateadd(minute, -30, @MyDate);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Jeffrey Williams (7/13/2008)
Hi, I posted a solution on Friday - but I guess I took too long writing it up and it was lost.

This happens to all of us from time to time and you should know that your "lost" text is recoverable, but only immediately afterward.

Whenever you get this message, immediately hit your browsers backup key to return the the posting page. All of your text should still be there (I use 3 different browsers and this works for me on all three). Just copy it out to Notepad or somewhere else safe and then start again.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

rbarryyoung (7/13/2008)
This happens to all of us from time to time and you should know that your "lost" text is recoverable, but only immediately afterward.
Whenever you get this message, immediately hit your browsers backup key to return the the posting page. All of your text should still be there (I use 3 different browsers and this works for me on all three). Just copy it out to Notepad or somewhere else safe and then start again.

Thanks - that would have worked very well if I hadn't gotten distracted by work :w00t:

One thing I realized after posting the above solution is that it will not utilize any indexes (if they exist). The following will use indexes and is possibly even a bit cleaner:

Declare @MyDate datetime;

Set @MyDate = '2008-07-13 11:00:00'; --Change this to get different results