locked
Send Query Results as HTML message RRS feed

  • Question

  • Dear All

     

    I have SQL Server 2005 installed in one of the PC. I wanted to know the process to get query results as HTML e-mail using Database mail. Please help me out.

    Monday, August 2, 2010 4:04 PM

Answers

  • Hi,

    Please refer to the following sample:

    CREATE TABLE #Requests1
    (
     TransID INT,
     CreateDT DATETIME,
     SystemName NVARCHAR(20),
     UniqueID INT,
     RequesttillDT DATETIME
    )
    GO
    
    INSERT INTO #Requests1 VALUES(1,'2010-07-01',N'Purchase',2,'2010-07-11')
    INSERT INTO #Requests1 VALUES(2,'2010-07-02',N'HR',12,'2010-07-12')
    INSERT INTO #Requests1 VALUES(3,'2010-07-03',N'SAP',15,'2010-07-13')
    GO
    
    DECLARE @tableHTML NVARCHAR(MAX) ;
    SET @tableHTML =
      N'<table><tr><th>Dear All</th></tr></table>'+
      N'<table><tr><th>the following requests are pending from your side.</th></tr></table>'+
      N'<table border="1">' +
      N'<tr><th>Request</th><th>CreationDate</th><th>SystemName</th><th>UniqueID</th> <th>DueDate</th> </tr>' +
      CAST(( 
    SELECT td=TransID,'',td=CreateDT,'',td=SystemName,'', td=UniqueID,'', td=RequesttillDT,'' FROM #Requests1
    FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX))+N'</table>'+
    N'<table><tr><th> Please do needful at the earliest</th></tr></table>'+
    N'<table><tr><th>regards</th></tr></table>'+N'<table><tr><th>IT Team</th></tr></table>';
     
     EXEC msdb..sp_send_dbmail 
         @profile_name='TestProfile',
         @recipients='example@hotmail.com',
         @subject='HTMLMailTest',
         @body=@tableHTML,
         @body_format = 'HTML' ;
    
    DROP TABLE #Requests1
    
    

     

    The result is:

    For more information, please see

    sp_send_dbmail (Transact-SQL)

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Marked as answer by KJian_ Monday, August 16, 2010 9:05 AM
    Wednesday, August 4, 2010 9:24 AM

All replies

  • You can use sp_send_dbmail and specify the parameter @body_format as 'HTML'.
    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Monday, August 2, 2010 4:08 PM
  • You can see this link :

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

     

     

    Monday, August 2, 2010 4:23 PM
  • One more way to do that is  to set column to HTML  format. and use that column as body of email.

    So what you can do is - insert dynamic values to column as html, and use that column as body of email.

    I jst completed this task at my job.

    so let me know if you need more help with it.

     

    Monday, August 2, 2010 4:58 PM
  • Hi,

    Could you please elaborate a bit? Could you please post your HTML format and sample data?

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    Wednesday, August 4, 2010 5:36 AM
  • HI Ai-Hua Qiu

     

    Thanks for your response.

     

    I wanted to have a mail similar to below

     

    Dear All

     

    the following requests are pending from your side.

    Request No Request Creation Date System Name Unique ID Due Date

    Please do needful at the earliest.

    regards

    IT Team

     

     

    The database table name is requests1 and fields are transid, createdt, systemname, uniqueid and requesttilldt.

     

     

     

     

     

     

    Wednesday, August 4, 2010 7:47 AM
  • Hi,

    Please refer to the following sample:

    CREATE TABLE #Requests1
    (
     TransID INT,
     CreateDT DATETIME,
     SystemName NVARCHAR(20),
     UniqueID INT,
     RequesttillDT DATETIME
    )
    GO
    
    INSERT INTO #Requests1 VALUES(1,'2010-07-01',N'Purchase',2,'2010-07-11')
    INSERT INTO #Requests1 VALUES(2,'2010-07-02',N'HR',12,'2010-07-12')
    INSERT INTO #Requests1 VALUES(3,'2010-07-03',N'SAP',15,'2010-07-13')
    GO
    
    DECLARE @tableHTML NVARCHAR(MAX) ;
    SET @tableHTML =
      N'<table><tr><th>Dear All</th></tr></table>'+
      N'<table><tr><th>the following requests are pending from your side.</th></tr></table>'+
      N'<table border="1">' +
      N'<tr><th>Request</th><th>CreationDate</th><th>SystemName</th><th>UniqueID</th> <th>DueDate</th> </tr>' +
      CAST(( 
    SELECT td=TransID,'',td=CreateDT,'',td=SystemName,'', td=UniqueID,'', td=RequesttillDT,'' FROM #Requests1
    FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX))+N'</table>'+
    N'<table><tr><th> Please do needful at the earliest</th></tr></table>'+
    N'<table><tr><th>regards</th></tr></table>'+N'<table><tr><th>IT Team</th></tr></table>';
     
     EXEC msdb..sp_send_dbmail 
         @profile_name='TestProfile',
         @recipients='example@hotmail.com',
         @subject='HTMLMailTest',
         @body=@tableHTML,
         @body_format = 'HTML' ;
    
    DROP TABLE #Requests1
    
    

     

    The result is:

    For more information, please see

    sp_send_dbmail (Transact-SQL)

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Marked as answer by KJian_ Monday, August 16, 2010 9:05 AM
    Wednesday, August 4, 2010 9:24 AM