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 ServerEach of these steps are explained below
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.
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 CreationCREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
Temp Table InsertionINSERT 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
' ' Rank Player Name Ranking Points Country
SET @body = @body + @xml +'
Temp Table ExecutionEXEC 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