MSSQL – How to send a Query result as an Email Attachment



How to create a query and email the results as an attachment

28 Comments

  1. take a look at vsql-email (sql-email.com), You cand send HTML formatted email from sql without enabling and configuring Database Mail and have more features like conditional color formatting, xlsx attachments etc

  2. When I try to run this query or one very similar I get the Error message that states "Failed to update database because the database is read only". Am I able to send an email with the results of my query if my database is in readonly?

  3. I have found an example code :

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Adventure Works Administrator',

    @recipients = '[email protected]',

    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder

    WHERE DueDate > ''2004-04-30''

    AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,

    @subject = 'Work Order Count',

    @attach_query_result_as_file = 1 ;

    for more info: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

  4. Use this code –

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '[email protected]',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
    WHERE DueDate > ''2004-04-30''
    AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

Leave a Reply

© 2023 53GB