Emailing results from a query is a common requirement for database profiling and ensuring data integrity. The SQL Server Agent allows you to set up email alerts to indicate if something has gone wrong.
In SSMS explorer find the SQL Server Agent. Right click and select “New Job…”
Begin by creating a new name for you job and ensuring that the owner is the one with elevated privileges on your server. Once saved jump to the “Steps” section. Here we could break up the individual steps for each operation. For this example we will use one step.
Using the following code we will be querying the database for our troubled data. We then want to dump the results of that query into a text file and email to the recipient.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Profile', @recipients = 'firstname.lastname@example.org', @subject = 'DB Issues', @query = ' SELECT * FROM table WHERE something_went_wrong ', @attach_query_result_as_file = 1, @query_attachment_filename = 'Issues.txt'
In the job “Schedule” window you can choose when you want your job to run.