sp_send_dbmail sends empty results

Question

When using sp_send_dbmail like this :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MY_PROFILE'
    ,@recipients = 'MY_EMAIL_ADDRESS'
    ,@query = 'SELECT TOP 50 FIELD1, FIELD2, FIELD3 FROM TABLE1'
    ,@subject = 'MY_SUBJECT'
    ,@body = 'This is a test'
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'file.csv'
    ,@query_result_separator = '    '
    ;

The attached files is always empty. I tried my query outside of sp_send_dbmail and it works perfectly. I also tried to replace my query by SELECT 1 and then my attached file has data.

What could cause my query to return no data?

Answer 1

I found how to solve it. I had to specify the database before the table name. Something more like :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MY_PROFILE'
    ,@recipients = 'MY_EMAIL_ADDRESS'
    ,@query = 'SELECT TOP 50 FIELD1, FIELD2, FIELD3 FROM DATABADE.dbo.TABLE1'
    ,@subject = 'MY_SUBJECT'
    ,@body = 'This is a test'
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'file.csv'
    ,@query_result_separator = '    '
    ;