April 21, 2014 at 7:24 am
I have xp_smtp_sendmail working fine with a simple job already.
I now want to run stored procedure that spits out a ton of data. How can I have this emailed via the xp_smtp_sendmail script? I'm stumped.
This is what I have so far, which isn't working. I'm over my head on this one.
DECLARE @rc int
DECLARE @results varchar (1000)
set @results = exec dbo.usp.AuditTrail_rpt;1 0, 217, -1, 'C', 'Mar 1 2014 12:00:00:000AM', 'Mar 31 2014 11:59:00:000PM', -1, -1
EXEC @rc=master.dbo.xp_smtp_sendmail
@from='sqlserver@domain.com',
@from_name='Auto Report',
@to='me@domain.com',
@replyto='do-not-reply@domain.com',
@subject='Auto Reportt',
@message= @results,
@server='exchange'
SELECT RC=@rc
---
It's bombing out on the set @results = exec. Is it easy to output a proc's results into an email with this xp_smtp_sendmail script?
April 21, 2014 at 7:44 am
It has nothing to do with the sendmail proc at all.
You can't assign the result of an EXEC to a variable like that.
One way would be to rewrite dbo.usp.AuditTrail_rpt to return the result set in an OUTPUT parameter, if it's plain text.
If it's a table you might be better off to store the result set in a global temp table and use the @query parameter.
As a side note: I strongly recommend to use sp_send_dbmail instead of xp_smtp_sendmail...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply