I have used this technique over and over, and although many other options exist, this is simple and effective so worth mentioning.
There is 1 prerequisite: database mail must be configured on the sql server.
The first step is to build your query- let’s say:
select blah from some_table where exception_condition = 'yes'
Once we’re happy with our query we can build it into the sp_send_dbmail command:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test', @recipients = 'firstname.lastname@example.org', @query = 'select blah from some_table where exception_codition = ''yes''', @subject = 'EBMS Exception Report', @attach_query_result_as_file = 1;
Notice- you will have to replace any single quotes in your query with 2 single quotes. You will likely notice a the output is a bit mis-aligned:
This is because sql server automatically extends the field to the length in the table definition. I normally truncate each field to no more than about 30 characters- and we’re in business. If you don’t want to keep running the sp_send_dbmail procedure, opening the e-mail, tweaking the query and re-running- you can run the sql query in management studio and select the "output as text" option as shown below:
After a bit of tweaking you should have it looking how you want:
Once you have tested this and are happy you can now created a sql job with a single t-sql step containing the above code. Attach a schedule and you’re good to go!
If you receive an error when trying to execute the sql job, you may need to double check the sql server agent account has adequate permission on the msdb database and the databases involved in your sql query.