[SOLVED] Can I select from a #temp table to define the @query attribute to dbmail?

Can I select from a #temp table to define the @query attribute to dbmail?

I'm getting an error when I try to attach a query as an attachment for an email. The query pulls from a temp table. The email works just fine without the attachment.

I've tried adding in @query_result_header = 1 which is the suggested fix for this error.

exec msdb.dbo.sp_send_dbmail @profile_name = 'data.production',

.......

,@query = 'select *
          from #pcpInfo
          order by pcp',
@attach_query_result_as_file=1,
@query_attachment_filename = 'MemberAttrition.csv',
@query_result_separator = ',',
@query_result_header = 1

I expected the email to come through with an a csv file attached, but instead I'm getting the following output error- "Failed to initialize sqlcmd library with error number -2147467259." Any suggestions?

Solution

Something like this should work:

Create table ##pcpInfo
(
    ID int
)
Insert into ##pcpInfo (ID)
Values (1)

exec msdb.dbo.sp_send_dbmail @profile_name = 'data.production',
@recipients = 'Email@Email.com'
,@query = 'select id
          from ##pcpInfo',
@attach_query_result_as_file=1,
@query_attachment_filename = 'MemberAttrition.csv',
@query_result_separator = ',',
@query_result_header = 1,
 @exclude_query_output = 0


Drop Table ##pcpInfo

This is changing your temp table to a global table, I have tested this and it works for me.

Edit

You also need to remove the order clause.