is it possible to send automated email every minute after querying from a database?

Question

I want to send an email alert after querying from the database(to check for some data).It should happen every minute.Is it possible to do it? How?

Answer 1

Yes, it is possible. In order for you the be able to send emails you need to configure either Database mail or an SMTP server. Having one of those components you can send email any time you want.

Answer 2

Yes,

You can setup a SQL Agent job to execute a query and send email every minute (or on whatever schedule you designate).

SQL Agent Job reference: http://msdn.microsoft.com/en-us/library/ms175138.aspx

Sending email from the database reference: http://msdn.microsoft.com/en-us/library/ms190307.aspx

As Paul said, you'll need to ensure that your environment is configured to be able to do this (see REMARKS section of second link)

Answer 3

You can schedule a SQL Agent job to run every minute to execute the needed T-SQL script. You might consider encapsulating the code in a stored procedure that sends the email (using Database Mail) conditionally and just execute that from the job. Example below.

CREATE PROC dbo.usp_check_for_alerts_and_send_email
AS
IF EXISTS(
    SELECT * FROM dbo.AlertTable
    )
BEGIN
    EXECUTE msdb.dbo.sp_send_dbmail
        @subject = 'Alert',
        @recipients = 'testaddress@example.com',
        @query = 'SELECT * FROM dbo.AlertTable';
END;
GO

Below is an example script to configure database mail that you can tweak for your environment, if you have not already done so.

-- Enable Database Mail for this instance
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs',1;
RECONFIGURE;
GO

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Primary Account',
    @description = 'Account used by all mail profiles.',
    @email_address = 'myaddress@example.com',
    @replyto_address = 'myaddress@example.com',
    @display_name = 'Database Mail',
    @mailserver_name = 'mail.example.com';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Default Public Profile',
    @description = 'Default public profile for all users';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Default Public Profile',
    @account_name = 'Primary Account',
    @sequence_number = 1;

-- Grant access to the profile to all msdb database users
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Default Public Profile',
    @principal_name = 'public',
    @is_default = 1;
GO

Answer 4

You want to send 1,440 emails a day??

Is it all going to one person? How quickly do you think the recipient will create a delete email rule?

Some ISPs might flag you as a spammer and cut your internet/email access off.

Would it make more sense to send the email when some parameter is out of an expected range?

Yes, it can be done but should it be done?