I need help sending out an automated email from SQL 2008 R2 server. I can get the email to work just fine using the following syntax, but I want to customize it better.
EXEC sp_send_dbmail @profile_name='administrator',
@subject='New KB Articles have been added',
@execute_query_database = 'kbaccess',
@body='The following has been added in the past day',
@query = 'Select id,title,created_by from faq where created_on > GETDATE()- 1'
1. When I email this report, it looks like crap. After researching, it appears that I need to format the report as html. I am having problems doing this. The examples I have seen, I'm having problems inserting my variables into their example variables. What I need is the above script to be send in a simple html table. I'll worry about the fonts and colors later, just a simple HTML table would be good. Please use my syntax above to help me write the exact script.
2. Is there a way to have this report run only if results are returned? Meaning if no rows are returned, can I have it cancel the email?