I start by creating a temporary table and populating it with some sample data, it is a list of top Tennis players along with their Rank, Name, Ranking Points and Country. This temporary table is used only in the example, in real time the temp table would not be required, instead you would use the actual database table which contains the data.
The next section is responsible for converting the SQL Server table data that we created into a local variable "@xml". In the select statement, each of the columns is going to be displayed as table data and rows in the email.
In another local variable "@body" we add the required HTML tags and text that is displayed in the email, in this case "Tennis Rankings Info". Then we include the column headers of the table inside the email. Remember that these table headers (column names) can be changed as per your convenience. In the example code, the column names of the temp table have been used as table headers. In you wish to display "Player" instead of "Player Name", you can do that by changing the table header tag to <th> Player </th>. Finally the closing HTML tags are added.
Once the HTML has been formatted I send the email using the system stored procedure sp_send_dbmail found in the msdb database and finally I drop the temporary table.
In order to use the code below, against your database table, you will have to replace the table name and column names of your table wherever necessary. Also, you will need to change the mail profile name and email address that you want to use.
Here is the sample code.
CREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
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 ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1>
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'bruhaspathy@hotmail.com', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp |
| Email Format |
Posts
How to change the color of the result set.. if the result is pass then that pass should be display in green if it is fail then it should display in red color...
ReplyDeletedo have an idea... please let me know
exelent
ReplyDeleteCheck below link for send data by mail
ReplyDeletehttp://sandipgsql.blogspot.in/2013/05/sql-server-send-sql-table-data-via.html