How to Send Email from SQL Server

How to send email from SQL Server?

Step 1) Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings.

Step 2)

RUN:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3)

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

To loop through the table 

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

    EXEC @query
    SELECT @id=MIN(id) FROM [email_adresses] where id>@id

END

Posted this on the following link http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

Send email via SQL Server Agent, based on the result of a T-SQL stored procedure

You could create an SQL Server agent job, with a t-sql step which uses msdb.dbo.sp_send_dbmail for sending an e-mail, when required (please see here, for the stored procedure complete reference).

Try something similar to the following:

DECLARE @HourDifference_output TINYINT;

EXEC dbo.sproc_XYZ_Notification @HourDifference_output OUTPUT;

-- SELECT @HourDifference_output AS HourDifferenceCaptured

IF @HourDifference_output > 12
BEGIN
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'db_mail_profile_i_have_already_created',  
        @recipients = 'intended-recipients@yourorganization.com',  
        @body = 'Too many hours difference.',  
        @subject = 'Automated Message' ; 
END

You must have already configured a database mail account, a database mail profile and granted appropriate access to the user running the job step. The second link also contains sample scripts for creating a database mail account and profile, adding the account to the profile and granting access appropriately (I personally prefer to configure database mail via the SSMS db mail wizard).

A decision to be made is whether to create a public or a private profile. You can find more information about the differences here.

Finally, it is, in my opinion, good practice to notify an administrator (via SQL Server Agent build-in mechanisms) when a job / step fails.

How to send an email on finding bad data in a table in sql server

Quick way would be use the @query argument with sp_send_dbmail and put that code in a SQL Server Agent Job.

Here's a simple example of what that sp_send_dbmail looks like:

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = ''  --This is specific to your setup
   ,@recipients = 'your@email.com'
   ,@subject = 'This is the subject'
   ,@query = 'select EmpID, FirstName, LastName from EmployeeData Where Active=1 and IsEmpTermed = 1' --The query
   ,@execute_query_database = ''  --The name of your database goes here where the query should be executed

That will basically execute the query you specified in @query, dump the results into an email and send it to whomever is in @recipients

If you don't know what the @profile_name is for your server configuration you can run

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

That will return a list of the mail profiles configured on your server. You will use the value from the "profile_name" column. If there are multiples, this is something I can't tell you as it is specific to your server configuration.

Once you have all that defined and working by manually running it in SSMS, go create a SQL Server Agent Job with a T-SQL step and add the "EXEC msdb.dbo.sp_send_dbmail" code. Defined a schedule at whatever frequency you would like it to run.

A lot of times I'll code it to check if the data issue exists before sending an email, so it will only send an email when the data issue exists so it won't keep spamming me if there are no issues, something like this

--The IF EXISTS checks if the data issue exists before sending an email
IF EXISTS(SELECT TOP 1 'x' FROM dbname.dbo.EmployeeData Where Active=1 and IsEmpTermed = 1)
BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = ''  --This is specifc to your setup
           ,@recipients = 'your@email.com'
           ,@subject = 'This is the subject'
           ,@query = 'select EmpID, FirstName, LastName from EmployeeData Where Active=1 and IsEmpTermed = 1' --The query
           ,@execute_query_database = ''  --The name of your database goes here
END

Send email from SQL server for each row of a dataset

Use a stored procedure. Inside the stored procedure use a cursor to get the details of the email and what you want to send. Call the sp_send_dbmail repeatedly until all employees get an email.

Okay, it took me a few minutes to set you up a test case database. We are sending emails to Scott Adams, Dave Letterman and Bill Gates.

/*  
    Setup test database.
*/

-- Use master
USE [master]
GO

-- Create a simple database using models attributes
CREATE DATABASE [MAIL];
GO

-- Use mail
USE [MAIL]
GO

-- Drop existing
IF OBJECT_ID(N'[DBO].[EMAIL_LIST]') > 0
DROP TABLE [DBO].[EMAIL_LIST]
GO

-- Create new
CREATE TABLE [DBO].[EMAIL_LIST]
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    EMAIL_ADDRESS VARCHAR(64),
    EMAIL_SUBJ VARCHAR(64),
    EMAIL_BODY VARCHAR(256),
    SENT_FLAG TINYINT DEFAULT (0)
);

-- Insert simple data
INSERT INTO [DBO].[EMAIL_LIST] (EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY)
VALUES
('scottadams@aol.com','Dilbert','What''s up scott?'),
('lateshow@pipeline.com','Late Show','Please read this letter Dave.'),
('billg@microsoft.com','Gates','How''s the weather in Seatle?');

-- Show the data
SELECT * FROM [DBO].[EMAIL_LIST];

This stored procedure reads the email list of unsent emails and sends out the emails.

/*  
    Create stored procedure
*/

-- Drop existing
IF OBJECT_ID(N'[DBO].[SEND_EMAILS]') > 0
DROP PROCEDURE [DBO].[SEND_EMAILS]
GO
 
-- Create new
CREATE PROCEDURE [dbo].[SEND_EMAILS]
AS
BEGIN

    -- Error handling variables
    DECLARE @err_number int;
    DECLARE @err_line int;
    DECLARE @err_message varchar(2048);
    DECLARE @err_procedure varchar(2048);

    -- ** Error Handling - Start Try **
    BEGIN TRY

    -- No counting of rows
    SET NOCOUNT ON;
    
    -- Declare variables
    DECLARE @VAR_ADDRESS VARCHAR(64);
    DECLARE @VAR_SUBJ VARCHAR(64);
    DECLARE @VAR_BODY varchar(256);

    -- Get email list
    DECLARE VAR_CURSOR CURSOR FOR
        SELECT EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY
        FROM [DBO].[EMAIL_LIST] 
        WHERE SENT_FLAG = 0;
        
    -- Open cursor
    OPEN VAR_CURSOR;

    -- Get first row
    FETCH NEXT FROM VAR_CURSOR 
        INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;

    -- While there is data
    WHILE (@@fetch_status = 0)
    BEGIN
        -- Send the email
        EXEC msdb.dbo.sp_send_dbmail 
            @recipients = @VAR_ADDRESS,
            @subject = @VAR_SUBJ,
            @body = @VAR_BODY,
            @body_format = 'HTML' ;  

        -- Grab the next record
        FETCH NEXT FROM VAR_CURSOR 
            INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;
    END

    -- Close cursor
    CLOSE VAR_CURSOR;

    -- Release memory
    DEALLOCATE VAR_CURSOR;            

    -- Update the table as processed
    UPDATE [DBO].[EMAIL_LIST] 
    SET SENT_FLAG = 1
    WHERE SENT_FLAG = 0;

    -- ** Error Handling - End Try **
    END TRY

    -- ** Error Handling - Begin Catch **
    BEGIN CATCH
       
      -- Grab variables 
      SELECT 
          @err_number = ERROR_NUMBER(), 
          @err_procedure = ERROR_PROCEDURE(),
          @err_line = ERROR_LINE(), 
          @err_message = ERROR_MESSAGE();

      -- Raise error
      RAISERROR ('An error occurred within a user transaction. 
                  Error Number        : %d
                  Error Message       : %s  
                  Affected Procedure  : %s
                  Affected Line Number: %d'
                  , 16, 1
                  , @err_number, @err_message, @err_procedure, @err_line);       

    -- ** Error Handling - End Catch **    
    END CATCH                
            
END

Looking at the MSDB.[dbo].[sysmail_mailitems] table, we can see the items were queued to go. This depends upon database mail being set up with a public default profile.

Sample Image

Scheduling the stored procedure via a job is up to you.


Related Topics

Split Values Over Multiple Rows

How to Replace (Null) Values with 0 Output in Pivot

Execute Immediate Within a Stored Procedure Keeps Giving Insufficient Priviliges Error

Primary Key or Unique Index

Delete SQL Rows Where Ids Do Not Have a Match from Another Table

Why Do People Hate SQL Cursors So Much

Explain Join VS. Left Join and Where Condition Performance Suggestion in More Detail

How to Get Second Largest or Third Largest Entry from a Table

New Line in SQL Query

MySQL Remove Duplicates from Big Database Quick

Postgres Unique Constraint VS Index

Oracle Query to Fetch Column Names

How to See All the "Special" Characters Permissible in a Varchar or Char Field in SQL Server

How to Compare SQLite Timestamp Values

Ms SQL "On Delete Cascade" Multiple Foreign Keys Pointing to the Same Table

What Is a 'Multi-Part Identifier' and Why Can't It Be Bound

Return Multiple Fields as a Record in Postgresql with Pl/Pgsql

SQL - Select First 10 Rows Only


Leave a reply



Submit