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
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: email@example.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
[Player Name] [varchar](128),
[Ranking Points] [int],
Temp Table Insertion
INSERT INTO #Temp
SELECT 1,'Palani Kumar',12390,'India'
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 Execution
@profile_name = 'SQLAlerts', -- replace with your SQL Database Mail Profile
@body = @body,
@recipients = 'firstname.lastname@example.org', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
Tennis Rankings Info
Rank Player Name Ranking Points Country
1 Palani Kumar 12390 India
2 xxxxxxx 7965 Switzerland
3 yyyyyy 7880 Serbia
No responses found. Be the first to comment...