CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsp-send-dbmail
David Hayward
David Hayward

Reputation: 189

SQL Server 2008 : sp_send_dbmail

I have the below SQL which runs daily in a daily job. I want the results in the email to have a line break at the end of each row from the query result.

Currently the email puts everything on the same line rather than one line per row in the SQL query

E.G if the query returns 10 rows, it should put it onto 10 lines in the email and not one continuous line

IF (select count(*) 
    from HSOfficeDocuments h
    where h.Expiry_Date <= dateadd(year, 1, getdate())) > 0
begin
    exec msdb.dbo.sp_send_dbmail
         @profile_name = 'OfficeNotificationsProfile',
         @recipients = '[email protected]',
         @subject = 'Expiring Office Documents',
         @query = 'select o.office, hs.document_type, h.Expiry_Date 
                   from officesystem.dbo.HSOfficeDocuments h
                   join officesystem.dbo.HS_Document_Type hs on hs.id = h.Document_Type_id
                   join officesystem.dbo.Offices o on o.id = h.office_id
                   where h.Expiry_Date <= dateadd(year, 1, getdate())',
         @query_result_header = 0,
         @body_format = 'HTML'
end

Upvotes: 0

Views: 241

Answers (1)

Aneesh
Aneesh

Reputation: 166

Can you try with the below query. As the mail body content support HTML we can use the HTML code to format result.

        IF (select COUNT(*) from HSOfficeDocuments h
        where h.Expiry_Date <= DATEADD(year, 1, GETDATE())) > 0
        begin

        exec msdb.dbo.sp_send_dbmail
        @profile_name = 'OfficeNotificationsProfile',
        @recipients = '[email protected]',
        @subject = 'Expiring Office Documents',

        @query = 'select o.office,  hs.document_type, h.Expiry_Date,''<br>'' from 
        officesystem.dbo.HSOfficeDocuments h

        join officesystem.dbo.HS_Document_Type hs on hs.id = h.Document_Type_id
        join officesystem.dbo.Offices o on o.id = h.office_id
        where h.Expiry_Date <= DATEADD(year, 1, GETDATE())',
        @query_result_header=0,
        @body_format = 'HTML'

        end

Upvotes: 5

Related Questions