sp_send_dbmail runs in the msdb database...so either your query needs to explicitly state teh database name, or the @execute_query_database must be supplied.
example :
@query = 'select FName as first,
LName as last
from PRODUCTION.dbo.client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
or
@query = 'select FName as first,
LName as last
from client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
@execute_query_database = 'PRODUCTION',
something like this might help a little:
EXEC msdb.dbo.sp_send_dbmail
@profile_name='GMail Profile',
@recipients = 'me@email.org',
@query = 'select FName as first,
LName as last
from PRODUCTION.dbo.client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.txt',
Lowell