Send Database Mails Using SQL Profile

Using SQL Server you can send Database mails. DB Mails are more secure that SQL mails because mails are in encrypted format. This new future in Database is includes after SQL Server 2005. Database Mail has many enhancement over SQL Mail. This mail is depend on SMTP (Simple mail transfer protocol). Database mail depends on Service Broker so this service must be enabled for Database Mail.
Here we will learn how to configure database profile to send database mails.
Step 1: Configuration Email to send mail:

  •     Connect to Database Engine.
  •     Expand Management Section.





  •      Double click on Database Mail and click Next.

  •      Select Manage Database Mail account and Profiles and Next.

  •      Select Create New Account.




  •      Add Account Name, Description, Email Address, Server Name and Port Number.

For Gmail Server Name: smtp.Gmail.com
Port Number:587
Important is check the check box  of property SSL.

  •      Click to next and finish. New Email profile is created.


Step 2 : Setting Database to send mail using configured email profile


  •     Double click on Database Mail.
  •     Select Set up Database Mail by performing the following task and click Next.


  •     Insert Profile Name and Description. 


  •      Click on Add Button and select the Email which we have configured in Step 1.


  •      Click to next and finish.

Step 3 : Send Mail
After creating profile now you can send mail. Before that you have to configure some properties to configure the Database Mail. For that we need to execute sp_configure  some stored procedure providing it some parameters. Below is the syntax for the same:
Syntax:
       sp_CONFIGURE 'show advanced', 1
     GO
     RECONFIGURE
     GO
     sp_CONFIGURE 'Database Mail XPs', 1
     GO
     RECONFIGURE
     GO

Below is the screen shot for the same:


To see the profile list in Database execute below stored procedure:
Syntax:
SELECT *FROM msdb.dbo.sysmail_profile


Now you can actually send mail using SQL Queries:


Syntax:
USE msdb
GO
EXEC sp_send_dbmail @profile_name='JasminPersonalMail',
@recipients='Jasmin.sayyad@gmail.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

Below is the screen shot for the same:



status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
Status and Log can be verified by using below queries:
Syntax:
SELECT *FROM sysmail_mailitems
GO
SELECT *FROM sysmail_log
GO







Comments

Post a Comment

Popular posts from this blog

Connecting Remote server with Local Server

Image
Introduction: Linked server is powerful functionality provided by SQL Server. We can create object of remote server on local server and use that object to perform operations on remote server. To explain this topic let's consider a scenario Scenario:   Local server having number of databases. We need to take backup of local databases and restored this database into the remote server. For this scenario we will follow certain steps: Step 1 : Create object of Remote Server Add remote server object in local server Syntax:     sp_addlinkedserver @server = N'LinkServer' ,     @srvproduct = N' ' ,     @provider = N'SQLNCLI' ,     @datasrc = N'Server Name ,     @catalog = N'Database Name' Here we have created object of remote server i.e. LinkServer . By using this object you can perform Select, Update functionality on the same. Step 2 : Grant Permission Next Step...
Read more

MDX Error handling tricks

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.
Read more

JSON_Automated_stored_procedure

Image
What is JSON: JSON stands for  J ava S cript  O bject  N otation, and is a way to store information in an organized, easy-to-access manner, "self-describing" and easy to understand. It gives us a human-readable collection of data that we can access in a really logical manner. Why we need JSON: JSON is a lightweight data-interchange format and it is language independent. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. Format of JSON string: JSON Example { "employees" :[     { "firstName" : "John" ,   "lastName" : "Doe" },     { "firstName" : "Anna" ,   "lastName" : "Smith" },     { "firstName" : "Peter" ,   "lastName" : "Jones" }             ] } You can compa...
Read more