1

all

May i ask is it possible to set two queries for the stored procedure sp_send_dbmail?

For example:

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'example',  
@recipients = 'example@XXX.com',  
@query = 'Select * from table1; Select * from table2;',  
@subject = 'Example',  
@attach_query_result_as_file = 1 ;

Since most of the example from the internet only contain one query for the @query parameter.

May i ask will the above code causing error?

Thanks a lot !!

EDIT:

After deployment, the script was result in error for the @query parameter defined.

The error message is shown below

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Solution:

Notice that when defining @query, the table should be in full path,

Hence:

@query = 'Select * from [YOUR_SERVER_NAME].[dbo].table1; Select * from [YOUR_SERVER_NAME].[dbo].table2;',

ProgrammingBaKa
  • 363
  • 2
  • 19
  • Did it cause an error when you ran it? – artm Jun 14 '17 at 07:27
  • Since i am using my company's development server, which have disabled dbmail service, so i can't test if there exists error :'( – ProgrammingBaKa Jun 14 '17 at 07:28
  • It should work. Source : https://stackoverflow.com/questions/16458610/sql-server-db-mail-which-can-send-mail-for-two-or-more-queries – Prabhat G Jun 14 '17 at 07:48
  • Thanks for the answer, i will keep this question open to see if someone actually try the code, and i will update the result when i deploy my script to the database – ProgrammingBaKa Jun 14 '17 at 08:38

1 Answers1

2

I tried . It did work, but the result was in same file. Not sure if thats what you want. Plus the number of rows is also an issue. Or error as follows is shown:

 Msg 22050, Level 16, State 1, Line 0
File attachment or query results size exceeds allowable value of 1000000 bytes.
Ranjana Ghimire
  • 1,785
  • 1
  • 12
  • 20