Sending Email based on a certian criteria within a table.

[nsanto17]

I need my SQL server to send an email based on certian criteria in a table is met.

i.e. Sales Price >= 200000 then send email.

I plan on adding a trigger to my table to insert some data into a Email_Notification table where it iwll set 1 field (Status) with a Bit datatype to a '0' indicating that the email was not sent. Once the email is sent then the Status field will be updated with a '1' indicating that the email was sent.

Whats the best way to do this? Stored_Proc? SSIS???

I need the email to be sent once my Email_Notification table gets inserted from the Trigger.

Thanks in advnace.

Nick

 

[gmmastros]

In my opinion, you have the basic building blocks set up properly. You do not want to send emails in a trigger. Instead, you should be loading data in to a table, exactly as you are doing.

As far as actually sending the emails, I would probably enable dbMail and then schedule a job that runs every 5 minutes checking for unsent emails. The job would run a stored procedure that sends the email.

By doing it this way, your email procedures will run separately from the code that is updating the original table, meaning that you will not see any performance degradation as a result of sending the email.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

George,

Thank you very much for the reply.

I have a trigger updating the Email_Transaction table. I then have a Trigger on that table running a the SP_SEND_DBMAIL. Is that what you mean. If not how would i go about scheduling a job to look at my Email_Transaction Table? Is this something i would do in SSIS? And then schedule that job to run?

 

[gmmastros]

Not exactly.

Suppose it takes 0.01 seconds to update the table, but it takes 3 seconds to send the email. Your trigger method will make it appear as though it takes 3.01 seconds to execute.

If you create a separate stored procedure that looks for unsent email and sends them, and then you schedule that process to occur every 5 minutes, there will be no lag when the user saves the data because it will only take 0.01 seconds.

Can you tell me what version of SQL Server you are using? Run the follow query and post the output here:

Select ServerProperty('ProductVersion'), ServerProperty('Edition')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

SQL Version 11.0.2100.60 (64 bit) - SQL2012.

 

[gmmastros]

ServerProperty('Edition') should have returned one of the following:

'Enterprise Edition'
‘Enterprise Edition: Core-based Licensing’
'Enterprise Evaluation Edition'
‘Business Intelligence Edition’
'Developer Edition'
'Express Edition'
'Express Edition with Advanced Services'
'Standard Edition'
'Web Edition'

Based on this page:

http://msdn.microsoft.com/en-us/library/ms174396.aspx

If you have Express edition, then you don't have "SQL Server Agent". The SQL Server agent is a service that runs on the server that activates your "jobs".

If you have one of the "paid for" versions of SQL Server, you should see a "SQL Server Agent" node in the object explorer window of SQL Server Management Studio. Expanding the SQL Server Agent node will show more nodes, one of which is "Jobs". you can use this to create a new job that runs on a schedule.

The "job" in this case would simply be to call a stored procedure on any interval you choose. The stored procedure would then check your email table for unsent emails and send them.

Does this make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

Makes total sense. I also have Standard Version so scheduling will not be an issue.

Am i creating my own SP on the Database or can i just call the SP_SEND_DBMAIL in my scheduling.

Thanks

Nick