Wednesday 22 May 2013

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

send mail by sql server
 Hi All, 
     In this article we will see how to send a sql table data via the mail body by using
sql store procedure.
Problem:
         Some time we need to send a sql table records via a mailbody in html format.
e.g- Send daily activity of jr person to Sr.person etc
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> 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> 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

12 comments:

  1. good example of sent sql table in html format by mail ,it is very usefull to me

    ReplyDelete
  2. This 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?

    ReplyDelete
  3. HI 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.

    ReplyDelete
    Replies
    1. yes you can,you just write procedure and create a job by using sql server agent
      check below link for creating a job

      http://msdn.microsoft.com/en-us/library/ms190268.aspx

      Delete
  4. hi i am creating a database and table in it.now where can i write the table format data and email sending data

    ReplyDelete
    Replies
    1. you create simple store procedure .for your understanding go step by step.you replace column name ,table name ,and credentials of mail sending profile .

      Delete
  5. HI sandip, I want get the 'select statement' from a table column , so then how can i attach 'td' to the stmt

    ReplyDelete
    Replies
    1. hi 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

      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))

      Delete
    2. `Declare @a varchar(max)
      Declare @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

      Delete
  6. for your better understandiing create table color by using columns (comid ,id ,names )
    and 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

    ReplyDelete
  7. 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.

    ReplyDelete
  8. I am getting error like profile name is not valid,,..please give me suggestion

    ReplyDelete

if you have any doubt any suggestions do comment