SQL Server Mail with Temp Table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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/

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

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

0	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

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

0	"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

Latest Posts