SQL Server 2008 : sp_send_dbmail

Question

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 = 'test@test.com',
         @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

Answer 1

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 = 'test@test.com',
        @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