Sql-server – How to send Email from a SQL Job with results of multiple select statements

sql serversql-server-2016sql-server-agentssms

I want to create a SQL job and send an emails with multiple select statements including in query results. Is it possible with SQL Job or should I use SSIS?

Edit 1:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = '****@abc.com', 
    @Execute_query_database = 'AdventureWorks',
    @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder  
                  WHERE DueDate > '2018-03-30'  
                  AND  DATEDIFF(dd, '2004-04-30', DueDate) < 2
              SELECT * FROM NorthWind.Production.Work  
                  WHERE DueDate > '2018-04-30'  
                  ' ,  
    @subject = 'Work Order Count',  
    @attach_query_result_as_file = 1 ;

Error message: File attachment or query results size exceeds allowable
value of 1000000 bytes.

Edit 2: I feel that SQL agent job not pointing to desired DB and sp_send_email has no database name. so I added @Execute_query_database = 'AdventureWorks' but still there is no improvement because in SQL SELECTS using two different databases.

Any thoughts?

Best Answer

You are basically sending 1GB file from sql server which is max. You can reconfigure the default using

exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','2000000'-- 2GB

I would say, use bcp out the file, compress it using zip or 7zip and then send using powershell or ssis.

Related Question