Solution::
We will see a step by step solution of this problem below.
step1 :
I start by creating a temporary table and populating it with some sample data
Create table #Temp
(
[Id] [int],
[Emp_Name] [varchar](128),
[salary] [int],
[department] [varchar](128)
)
Insert into #Temp
select 1,'SA',12390,'INDIA'
union all
select 2,'SA',7965,'INDIA'
union all
select 3,'SANDIP',7880,'INDIA'
union all
select 4,'GAIKWAD',12390,'INDIA'
union all
select 5,'SAGRIKA',7965,'INDIA'
union all
select 6,'SAVITA',7880,'INDIA'
step2 : by bellow statement we will select a data from a table in Html format.
declare @xmlnvarchar(MAX)
declare @body nvarchar(MAX)
set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
[salary] AS 'td','', department AS 'td'
from #Temp order by ID
for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
set @body ='<html><body><H3>Employee Info</H3>
<table border = 1>
<tr bgcolor="#FF0000">
"th"> <th> ID <th> Emp_Name </th> <th> salary </th>
<th> department
</th></tr> '
set @body = @body + @xml +'</table></body></html>'
print @body
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.
A email sending part is bellow
exec msdb.dbo.sp_send_dbmail 'IMRAN' , @recipients = 'sandip.gaikwad@bbraun.com', @copy_recipients = '',
@blind_copy_recipients='',
@subject = 'DATA IN TABULAR FORMAT....' , @body = @body , @body_format = 'HTML'
drop table #Temp
final query:
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
(
[Id] [int],
[Emp_Name] [varchar](128),
[salary] [int],
[department] [varchar](128)
)
Insert into #Temp
select 1,'SA',12390,'INDIA'
union all
select 2,'SA',7965,'INDIA'
union all
select 3,'SANDIP',7880,'INDIA'
union all
select 4,'GAIKWAD',12390,'INDIA'
union all
select 5,'SAGRIKA',7965,'INDIA'
union all
select 6,'SAVITA',7880,'INDIA'
declare @xmlnvarchar(MAX)
declare @body nvarchar(MAX)
set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
[salary] AS 'td','', department AS 'td'
from #Temp order by ID
for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
set @body ='<html><body><H3>Employee Info</H3>
<table border = 1>
<tr bgcolor="#FF0000">
"th"> <th> ID <th> Emp_Name </th> <th> salary </th>
<th> department
</th></tr> '
set @body = @body + @xml +'</table></body></html>'
exec msdb.dbo.sp_send_dbmail 'IMRAN' , @recipients = 'sandip.gaikwad@bbraun.com', @copy_recipients = '',
@blind_copy_recipients='',
@subject = 'DATA IN TABULAR FORMAT....' , @body = @body , @body_format = 'HTML'
drop table #Temp
good example of sent sql table in html format by mail ,it is very usefull to me
ReplyDeleteThis article is very well. I think It is useful. I seek a query. It must host a html table but Its reason's table must vertical. Do you have an answer this issue?
ReplyDeleteHI i extracting one report every Wednesday. Is there any way i can get the report directly to my mail id or some mail ids at a specific time such that the report will run by itself and come to mail id at a given time.
ReplyDeleteyes you can,you just write procedure and create a job by using sql server agent
Deletecheck below link for creating a job
http://msdn.microsoft.com/en-us/library/ms190268.aspx
hi i am creating a database and table in it.now where can i write the table format data and email sending data
ReplyDeleteyou create simple store procedure .for your understanding go step by step.you replace column name ,table name ,and credentials of mail sending profile .
DeleteHI sandip, I want get the 'select statement' from a table column , so then how can i attach 'td' to the stmt
ReplyDeletehi Marc rim,use below statement to attach td to your column data ,replace column name and table name from below statement with your table and column name . for better understanding check step 2 in current post
Deletedeclare @xmlnvarchar(MAX)
declare @body nvarchar(MAX)
set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
[salary] AS 'td','', department AS 'td'
from #Temp order by ID
for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
`Declare @a varchar(max)
DeleteDeclare @b varchar(max)
set @a = cast((
SELECT td = f1, '', td = f2 , '' from tablenme Where f2 is null order by f1
for XML PATH('tr'), ELEMENTS )AS nvarchar(max))
"set @b ='
Stayed on the list
f1
f2
'"
set @b = @b + @a +''
Print @b'
I want to get the total select stmt used in above code from a table field.
`SELECT td = f1, '', td = f2 , '' from tablenme Where f2 is null order by f1`
Note: i have changed the html tags as this site is not accepting
for your better understandiing create table color by using columns (comid ,id ,names )
ReplyDeleteand use below query
declare @allrecord varchar(max)
declare @finaltable nvarchar(MAX)
declare @header nvarchar(max)
----------This is only for display header row (column Name) -------
set @header ='<'tr bgcolor="#FF0000"> <'th> ID <'th> Emp_Name <'th> salary '
----------this is for actual data rows-----------------------
set @allrecord = cast(( select [comid] AS 'td','',[id] AS 'td','',
[Names] AS 'td',''
from color where id >0 order by ID
for XML PATH('tr'), ELEMENTS ) as nvarchar(MAX))
------------------------this is final table --------------------
set @finaltable ='<'table border="1">'+@header +@allrecord+ ''
print @finaltable
Hi Sandeep, How to add colours based on value in your same code? Like for example for true background color should be red and for false it should be blue like that.
ReplyDeleteI am getting error like profile name is not valid,,..please give me suggestion
ReplyDelete