SQL Query results delievered by email

Bonus12 1,116 Reputation points
2022-11-09T01:12:18.963+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,138 questions
0 comments No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. YufeiShao-msft 7,076 Reputation points
    2022-11-09T08:05:58.753+00:00

    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.

    0 comments No comments