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?
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.