How to Configure SQL SERVER Database mail and send mail using SQL Database Mail

Here i am going to be explain how we can Setup or Configure SQL Server Database Mail. And we will try to send SQL Query Output in mail via using SQL Database Mail.

Description with Script:-


Below key points will help us to Configure mail setup in SQL Servers:

• We will use System Default Database MSDB, because all mail configuration information maintained in it.
• sp_send_dbmail store procedure : This is System default SP & store all mail features. And we can change features as per our need.

In this Article we are going to be configured SQL SERVER Database Mail. It help us to send mail alert to SMTP servers. Help of this we can send of query output in mail. It has lots of features but here i only write about send SQL query output as in mail. 

How to configure SQL Database Mail Alert:

Steps:-1

Open SQL Server and explore the Management Tab, then right click on Database Mail and click on Configure Database Mail link Screen shot as below for your ref.

Steps:-2

Steps:-3

Step:4

Here we will Add profile Name for Database Mail, screen shot as below:-

Step:5

Here we will write SMTP details for mail id's, write Mail id & Password details here.

Step:6

Now your profile has been created, now click on Next button to configure more features.

Step:7

Tick Yes on default profile as below screen shot then click on Next button.

Step:8

It is optional, you can set Retry attempts value in Send mail failure case then click on Next button.

Step:9

Step:10

Now Database Mail profile has been configured successfully, so we will test DB mail profile is working or not??

Step:1

Right click on Database Mail and click on Send Test E-Mail.

Step:2

Select DB Profile and Write receiver mail id and click on send Test E-Mail.

Step:3

Click on OK button

Step:4

Now check your mail, mail received successfully. Output as below

Now we will send SQL Query Output as in Mail:-

Before going to start remember below points:-

• Here we will use System Default Database MSDB, because all mail configuration information maintained in it.
• sp_send_dbmail store procedure : This is System default SP & store all mail features. And we can change features as per our need.

Script as below:-

• Change Profile name which you were entered at the time of Database mail Configuration.
• Change Recipients mail id's, you can enter multiple mail id's using ; separator.

----------------Database Mail Sent Sript-------------------------

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

DECLARE @Table NVARCHAR(MAX) = N''

SELECT @Table = @Table +'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() 

OVER (ORDER BY [CustomerID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END +';">' +

'<td>' + CAST([CustomerID] AS VARCHAR(100))+ '</td>' +

'<td>' + [ContactName]+ '</td>' +

'<td>' + [City]+ '</td>' +

'</tr>'

FROM Test.dbo.Customers

SET @tableHTML = 

N'<H3><font color="Black">Till Date Registered Data</H3>' +

N'<table border="1" align="left" cellpadding="2" cellspacing="0" 

style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +

N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">

<th>CustomerId</th>

<th>CustomerName</th>

<th>City</th>

</tr><tr>'

 + @Table + N'</table>' 

EXEC msdb.dbo.sp_send_dbmail--- Use MSDB database

@profile_name = 'DBAlert',--- Write your Mail Profile which was you configured in Database Mail

@recipients = 'abc@gmail.com;', -- Write Recipient Email Id's, you can write multiple email id's with using ; seprate

@body = @tableHTML,

@body_format = 'HTML' ,

@subject='Auto Mail Alert';--- set Subject for Alert

Script screen shot as below:

After execution of above script, check your mail. Output as below:-

How we can get complete MONTH name from date in SQL

DATENAME function :

It will return a specified part of a given date in string format. here we will get month name from DATENAME function. 

Query as below:

SELECT  DATENAME(month,GETDATE()) as MonthName---Month Name
SELECT  DATENAME(mm,GETDATE()) as MonthName---Month Name
SELECT  DATENAME(m,GETDATE()) as MonthName---Month Name
select left(datename(month,getdate()),3)as MonthName-- Month Name first three Abbreviation
SELECT  DatePart(mm,GETDATE()) as MonthNumber -- Month Number

Output screen shot as below:-

SQL full database BACKUP through SQL SCRIPT

We can take multiple database backup in one script execution. Script as Below:

SQL Backup SCRIPT:

DECLARE @DBname VARCHAR(50) -- DB database name

DECLARE @DBpath VARCHAR(256) -- DB backup files path

DECLARE @DBfileName VARCHAR(256) -- DB filename for backup

DECLARE @DBfileDate VARCHAR(20) -- ADD datetime with filename

-- set database backup path

SET @DBpath = 'C:\Backup\'

-- Set file name with datetime

Select @DBfileDate=CONVERT(varchar(20),GETDATE(),112) + REPLACE(CONVERT(varchar(5),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name IN ('data','Test') -- select database name for backup

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DBname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DBfileName = @DBpath + @DBname + '_' + @DBfileDate + '.BAK'

BACKUP DATABASE @DBname TO DISK = @DBfileName

FETCH NEXT FROM db_cursor INTO @DBname

END

CLOSE db_cursor

DEALLOCATE db_cursor

In above script CURSOR will help us to select multiple database one by one. After execution of query Output as below:-

Role of IDENTITY in SQL

IDENTITY:-  

•  Identity means Unique value in column.
• An Identity column value increase automatically in  SQL server table. 
• An Identity column value is created by SQL server.
• By default an identity value is 1.

Syntax:-

IDENTITY(seed value, Increment value)

Seed value: Starting value of a column, by default is 1.

Increment value: Default value is 1 and how many incremental value you want to add in previous identity value of the record.

Now we will create table with few records

CREATE TABLE Test  

(  

   ID INT IDENTITY(1,1),  

   name [varchar](MAX) NOT NULL,  

   Mobieno varchar(50) NOT NULL  

)  

INSERT INTO Test VALUES ('Test1', '9089789878');  

INSERT INTO Test VALUES ('Test2', '9878987879');  

INSERT INTO Test VALUES ('Test3', '7898789878');  

select * from Test

In above output Identity Incremental value is one, now we will change the value.

For this firstly we will drop ID column & will add again with different incremental value.

Query as below:

ALTER TABLE test DROP COLUMN ID; 

Now we will add again ID column with different Identity incremental value.

ALTER TABLE test ADD ID INT IDENTITY(1,2);  

In above query incremental value is 2.

Now select records from tables:-

select * from Test

Output as below:-

How to use PIVOT in SQL

PIVOT in SQL:-

• Pivot help us to transform or display data row  to column level. 
• It help us to generate Multidimensional report.
• We can generate Month & Year wise summary of large data.

Here we are going to display customer data month wise.

Query as below: -

Select * from CustomerData

Now we will use PIVOT in query:-

SELECT *

FROM (

SELECT name, mobileno, left(datename(month,dtDate),3)as [month], amount FROM CustomerData

) as s

PIVOT

(

SUM(amount)

FOR [month] IN (jan, feb, mar, apr,

may, jun, jul, aug, sep, oct, nov, dec)

)AS pvt
Query output as below:-

SQL Min() & Max() functions

SQL MAX functions:- It will return largest value of the selected column.

Query :-  select MAX(columnname) as ID  from tablename

SQL MIN functions:- It will return smallest value of the selected column.

Query :-  select MIN(columnname) as ID  from tablename