1

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?

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

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.

  • Thanks. I changed Maxfilesize but still it throws same error. I thought of using compression and send through power shell. – CR241 Apr 6 '18 at 0:34
  • Does your mail server have size limit ? – Kin Shah Apr 6 '18 at 0:48
3

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!

2

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.