How to Send Mail From Database Engine in SQL Server


Here I shared About mail setup from Database Engine component of SQL Server.
* I am using database mail set up wizard to enable it.
* In setting up database mail, profile creation, account set up, configuring the account and sending test email


How to Send Mail From Database Engine in SQL Server



In setting up database mail, profile creation, account set up, configuring the account and sending test email are the main steps. Note: THis Mail Feature not available in Express Edition.Database mail in SQL Server 2008 is an enterprise solution that is used to send emails from Database Engine component of SQL Server

Each of these steps are explained below


  • Connect to the SQL Server Management Studio.

  • In the Object Explorer, Go to Management Node, expand Management Node, and Point to Database Mail option

  • Right Click on Database Mail, and then Click on Configure Database Mail

  • Once Configure Database Mail option is clicked, Database Mail Configuration Wizard appears. This wizard helps to set up the database mail in SQL Server Instance.
  • Click Next on Database Mail Configuration Wizard as shown above.
    Select Configuration Task window comes when Next button is clicked as shown above. Since am setting up Database Mail for the first time therefore am selecting the Set up option as shown in the figure below.

  • In this set step, new profile can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.

  • Click Next after select the set up option.

  • When you click Next, Following window comes since Database Mail is not enable on my instance so am going to enable it from here instead of using system stored procedure or configuration manager as I mentioned earlier

  • Click Yes as marked in Red to enable the Database Mail and the next comes a window where we can create New Profile for the Database mail and then add new SMTP accounts for this profile.

  • Profile name is used by different users to send out email notifications. As shown in the figure below, Profile name and Description of the Profile are created.

  • The name of the Profile that I used is: SQLServer2008 Database Mail Profile. Description is an optional but it is good to add description about the profile

  • After profile name and description of the profile is added, we can add the SMTP Accounts by Clicking on Add…. button as shown in New Database Mail Account Window figure below.

  • Account name is Database Mail, Description is an optional. I have used my Gmail account as an Outgoing SMTP since I do not have mail server set up on my machine.

  • Under Outgoing Mail Server SMTP:
    E-Mail Address: xxxxxxxx@gmail.com (Your Email id)
    Display Name: SQL Server
    Reply Email: It can be blank or we can use same email as above.
    Server Name: smtp.gmail. com, this is SMTP server.
    Port Number: Gmail smtp server port number to be used is 587 but default port number is 25.
    Secure Connection: We have to select SSL connection as shown in the picture for gmail.
    Basic Authentication: Provide gmail account and password for this account.

    Here Sample SQL Query For Database Mail


    Temp Table Creation

    CREATE TABLE #Temp 
    (
    [Rank] [int],
    [Player Name] [varchar](128),
    [Ranking Points] [int],
    [Country] [varchar](128)
    )


    Temp Table Insertion
    INSERT INTO #Temp
    SELECT 1,'Palani Kumar',12390,'India'
    UNION ALL
    SELECT 2,'xxxxx',7965,'Switzerland'
    UNION ALL
    SELECT 3,'yyyyy',7880,'Serbia'

    Temp Table Stored Procedure
    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
    [Ranking Points] AS 'td','', Country AS 'td'
    FROM #Temp ORDER BY Rank
    FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

    SET @body ='

    Tennis Rankings Info




    '

    SET @body = @body + @xml +'
    Rank Player Name Ranking Points Country
    '

    Temp Table Execution
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLAlerts', -- replace with your SQL Database Mail Profile
    @body = @body,
    @body_format ='HTML',
    @recipients = 'xxxx@gmail.com', -- replace with your email address
    @subject = 'E-mail in Tabular Format' ;


    Output


    Tennis Rankings Info
    Rank Player Name Ranking Points Country
    1 Palani Kumar 12390 India
    2 xxxxxxx 7965 Switzerland
    3 yyyyyy 7880 Serbia


    Comments

    No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: