SQL Query results delievered by email

Question

Hi All,

I'm running SQL server 2019 and I have a query that runs with no issues against an application database. I'd like to run this query on a schedule and send the results to an email.

what is the best way of doing that? is it using SQL procedure?

Thanks

Answer 1

You can use the system SP sp_send_dbmail (Transact-SQL) to send a query result per email.
To schedule it use a SQL Server-Agent job.

Answer 2

Hi @Bonus12 ,

You can try to use SQL Server agent and sp_send_dbmail, set up a job and ues sp_send_dbmail with the @Query parameter, you can try to sys.dm_os_sys_memory DMV to gather metrics and use sp_send_dbmail to send the results of the query

Please refer to this doc:
Email Query Results Using a SQL Server Agent Job

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'Main DB Mail profile',  
@recipients = 'the list of email addresses to send the email to',  
@subject = 'subject name',  
@query = N'such as: SELECT total_physical_memory_kb, available_physical_memory_kb, system_memory_state_desc, collection_date_time  
FROM MemoryDMVHistory  
WHERE CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE)  
ORDER BY collection_date_time DESC;',  
@attach_query_result_as_file = 1,  
@query_attachment_filename = 'Memory Values.txt'  

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.