SQL Server Performance

need help to automate my report using database mail fin in SQL 2005

Discussion in 'SQL Server 2008 General Developer Questions' started by Kohila, Sep 29, 2009.

  1. Kohila New Member

    Hi SQL Experts,
    I am tring to send a automated email to my client with daily report using the database mail functionality in the SQL server 5000.

    I am able to recive the email with the report but it is in the unformatted way.

    Here is the queries which i used to send the email when i exceute my stored procedure with msdb.dbo.sp_send_dbmail.

    Stored procedure:

    CREATE procedure[dbo].[sp_user_sessionreport]

    Select B.EIN as EIN, B.OUC as OUC, B.PROFILE_ID as [USER ROLE] , B.LOCATION_CODE as [LOCATION CODE] , B.MACHINE_IP as [MACHINE IP], B.LOGIN_DATE as [LOGIN DATE] , B.LOGOUT_DATE as [LOGOUT DATE],(SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,' ') THEN 'CLOSED' ELSE 'OPEN' END FROM tblsession_log_trn A WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID) as [Session Status] from tblsession_log_trn B WHERE B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1) and CONVERT(VARCHAR(10), GETDATE())

    exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Database Mail',
    @recipients = 'kohilavani.murthy@email.com,
    @subject = 'Test',
    @body = 'HTML',
    @query = 'EXEC sp_user_sessionreport',
    @execute_query_database = 'database_name';

    I am reciving the email with the report in unformatted way
    ===========================================================

    EIN OUC USER ROLE LOCATION CODE
    MACHINE IP LOGIN DATE LOGOUT DATE Session Status
    -------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- --------------------------------
    -------------------------------------------------------------------- -------------------- ----------------------- ----------------------- --------------
    603711006 MYZU11 advisor TLP-DD
    10.229.255.20 2009-09-28 12:43:13.473 2009-09-28 19:23:49.300 C
    LOSED
    603821248 MYZU10 advisor TLP-DD
    10.229.242.20 2009-09-28 12:43:58.100 2009-09-28 16:28:23.267 C
    LOSED
    604743921 MYZC91 advisor BCK-CT
    10.236.152.134 2009-09-28 12:44:02.227 2009-09-28 12:44:51.210 C
    LOSED
    604744003 MYZC91 advisor BCK-CT
    10.236.152.136 2009-09-28 12:44:43.400 2009-09-28 15:55:15.077 C
    LOSED
    603091160 MYZNA1 advisor NCC-NE
    10.230.231.110 2009-09-28 12:46:35.747 2009-09-28 21:13:47.447 C
    LOSED
    701730831 MKNPB MANAGER ACC-BT
    147.149.1.202 2009-09-28 12:54:59.247 2009-09-28 13:00:24.617 C
    LOSED
    700774300 MYZDBC advisor DCC-DN
    10.230.250.36 2009-09-28 13:01:06.743 NULL OPEN
    702388406 MKNPF advisor ACC-BT
    147.149.1.202 2009-09-28 13:02:08.340 2009-09-28 13:06:13.080 C
    LOSED
    603538207 MYZU11 advisor TLP-DD
    10.229.255.79 2009-09-28 13:02:41.497 2009-09-28 20:57:13.510 C
    LOSED
    604185844 MYZAC4 advisor ARM-BT
    147.149.1.202 2009-09-28 13:02:49.217 NULL OPEN
    702235786 MYZGA7 advisor ABH-G2
    10.232.182.171 2009-09-28 13:02:54.497 2009-09-28 13:07:32.270 C
    LOSED

    I have tried to use HTML tags but dint find a solution. Could any one assist me on this please.
  2. manikon New Member

    Hi,
    Follow the given steps to make HTML formatted mail.
    1.Declare table variable, say Declare @TableName TABLE (col1..,col2..,col3..)

    2.Push the records to the defined table variable, @TableName

    3.If you need stylesheet then push the style tags into a variable say, @Style_text

    4. Form a Xml query using 'FOR XML PATH' as shown below and push it into a variable
    SELECT @TableHTML = @Style_text+
    N'<table border="1">' +
    N'<tr><th>Header1</th><th>Header2</th><th>Header3</th></tr>'+
    CAST ( ( SELECT
    td = Col1, '',
    td = Col2, '',
    td = Col3, ''
    FROM @TableName
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )
    5. Finally Execute sp_send_dbmail as shown below
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = @ToMailId,
    @copy_recipients = @CCMailId ,
    @blind_copy_recipients = @bccmailid,
    @subject = @subject_tmp,
    @body = @TableHTML,
    @body_format = 'HTML' ,
    @exclude_query_output = 1;
  3. Kohila New Member

    Hi Mani/Satya
    Many thanks for your assistance. Now i am able to generate the report with HTML format...
    But still..i am finding an answer how to dispay the null valuse in my table....
    For example.In my report i have a column called logout date, when the logout date is null for a record then my report should diaplay an empty cell for logout date.
    I have tried with case with null..but could works...can you please give me your suggesstions.
    Thanks Kohila
  4. Kohila New Member

    Also my login date and logout date is displayed like 2009-10-02T08:09:35.143. What to remove the T. This is not present when i execute the query in SQL server only this issue appers when sending email in HTML format.
    Please advice with your suggestions
  5. manikon New Member

    Did you tried using ISNULL to replace the NULL values to empty string? It should work.
    Also for the date format problem, use convert function to change the datetime values to character with your required date format.
  6. Kohila New Member

    Hi Mani,
    Yes i tried out this below by using the ISnull,but instead of 'blank' value '1900-01-01T00:00:00' value is dispalyed.select
    B.EIN , B.OUC , B.PROFILE_ID , B.LOCATION_CODE , B.MACHINE_IP , B.LOGIN_DATE ,ISNULL(CONVERT(varchar(19), logout_date, 120), ''),(SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,' ') THEN 'CLOSED' ELSE 'OPEN' END FROM tblsession_log_trn A WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID) as [Session Status] from tblsession_log_trn B WHERE B.LOGIN_DATE BETWEEN CONVERT(varchar(20), GETDATE()-1, 103) and CONVERT (VARCHAR(20), GETDATE(), 103)
  7. Kohila New Member

    Yes now it work, I have done some mistakes in the declaration part.
  8. Kohila New Member

    Hi Mani,
    Can i have your advise as how i can add a message in the top of my email.
    No of open session : 53(this value should ge get from table by select count (coulmn) from table)
  9. manikon New Member

    You can concatenate this text in the For XML select statement itself.
    (ex)
    SELECT @TableHTML = @Style_text+
    N' <H7>No of open session : ' + @opensession +
    N'<table border="1">' +
    N'<tr><th>Header1</th><th>Header2</th><th>Header3</th></tr>'+
    CAST ( ( SELECT
    td = Col1, '',
    td = Col2, '',
    td = Col3, ''
    FROM @TableName
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )
  10. Kohila New Member

    Hi Mani,
    I have completed my report successfully now. Many thanks[:)]
  11. satya Moderator

    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
    FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

    SET @body ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>'
    SET @body = @body + @xml +'</table></body></html>'

    EXEC msdb.dbo.sp_send_dbmail
    @recipients =N'chapman.tim@gmail.com',
    @body = @body,
    @body_format ='HTML',
    @subject ='Message Subject',
    @profile_name ='DatabaseMailProfile'

Share This Page