Search code examples
sqlsql-servert-sqlsp-send-dbmail

Sending dbmail with a query result with an okay format

Im trying create an automated mail with my query results, but the formatting is messed up and it's barely understandable. The query returns 10 columns with aproximately 10 lines. Is there any good way to easily format it better? It doesn't matter if it's an attached file or in the mail body(maybe a .txt file?).

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='Notification', 
@recipients='test@test.com', 
@subject='Updated', 
@body = 'Hi this is an automated mail', 
@query = 'SELECT * FROM [TestDB].[dbo].[TestTable]',
@attach_query_result_as_file = 0

Thank you!

Br Andreas

Solution

  • As you can see here, you can format your body in HTML-style before calling sp_send_dbmail. In that case you don't pass the @query-parameter but add @body_format='HTML'.