SQL Server Mail with Temp Table

Posted by Murugavelmsc under Sql Server on 9/19/2013 | Points: 10 | Views : 2061 | Status : [Member] | Replies : 5
Hi Expert,

I have a summary details of employee

Dept No. of employee TrainingAttended
IT 100 58
HR 50 28

Based on the procedure I have arrived the count and save in temporary table.

But I want to send in mail...
I will send a parameter of From, To, Subject, BodyText ...

BodyText as Temporary Table

Based on this format in HTML table
How to constuct a procedure for such scenario...

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link
http://www.wikihow.com/Send-a-Scheduled-HTML-Report-Directly-from-SQL-Server

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
for DB Mail configuration follow this link
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server
http://www.emailarchitect.net/easendmail/kb/sql.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Sql server- send sql table data via email using store procedure

Sample code

--Sample Temp Table creation 

Create table #Temp
(
[Id] [int],
[Emp_Name] [varchar](128),
[salary] [int],
[department] [varchar](128)
)

--Insert data into the table
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'

-- script to form HTML
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> ID <th> Emp_Name </th> <th> salary </th> <th> department
</th></tr> '
set @body = @body + @xml +'</table></body></html>'

-- Sending email by using procedure msdb.dbo.sp_send_dbmail
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



For another sample,
http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I'm posting the direction which you should follow to send the mail via stored prodecure..
CREATE PROCEDURE USP_SendEmail

(
@from ....,
@To ....,
@body ....
.
.
.
)
AS
BEGIN
-- Query to return results
-- Format as HTML tabular code
-- pass each param value to msdb.dbo.sp_send_dbmail
-- 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'
END


Let me know the further updates

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark As Answer" if you sorted out the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response