0

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?

Shadowxvii
  • 1,080
  • 2
  • 12
  • 31
  • I've had the issue before and it came down to data types of the fields in the @query line...would need table schema to confirm that. If you are into trial and error...try: SELECT TOP 50 FIELD1 FROM TABLE1' then try SELECT TOP 50 FIELD2 FROM TABLE1' then try SELECT TOP 50 FIELD3 FROM TABLE1' If I'm right...one of those will return empty data set and you'll know which column is creating the error. – Twelfth Sep 04 '13 at 23:05
  • I had already tried with only FIELD1, but got empty results too. I tried with every field individually and got empty results every time. For info, FIELD1 is an Int while FIELD2 and FIELD3 are varchar(8). – Shadowxvii Sep 05 '13 at 14:33

1 Answers1

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 = '    '
    ;
Shadowxvii
  • 1,080
  • 2
  • 12
  • 31
  • That makes complete sense...sp_send_dbmail will be in the MSDB database and not the one you are querying. Instead of throwing a 'table not found' error it simply sends blank results. Good on you. – Twelfth Sep 05 '13 at 16:22