How to send Email from a SQL Job with results of multiple select statements

Question

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?

• James: Your results are too big, and you re hitting the limit set up in your database mail. You can up it, probably.
• CR241: @James I changed to 2000000, but still ended up with same error. Actually I am trying to pull >10MB data
• Colin Douglas: I've updated my answer below to reflect your edits
• Aaron Bertrand: What person on earth wants an e-mail attachment this big?
• CR241: @AaronBertrand.. who said NO to receive an email attachments size from 1KB to 10MB. any restrictions in SQL server?

Answer 1

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.

• CR241: Thanks. I changed Maxfilesize but still it throws same error. I thought of using compression and send through power shell.
• Kin Shah: Does your mail server have size limit ?

Answer 2

To answer your question it is possible to have many select statements in your query, I am not aware of a limit on the number.

Your first query would execute without any issue but your second query is creating a result set bigger than your settings allow so it will not send, I agree with @kin that you would be better off saving the results down somewhere and then email a link. As a bonus your network administrators will be happier too!

Answer 3

Terminate your T-SQL statements appropriately in your @query parameter with a semicolon and you'll get both result sets in your attachment (assuming that the result sets are smaller than your attachment limit size). You'll also need to escape your date values.

EXEC msdb.dbo.sp_send_dbmail 
 @profile_name = 'Adventure Works Administrator', 
 @recipients = '****@abc.com', 
 @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 ;