March 25, 2016 at 4:44 am
Hi All,
My issue is the following:
O.S. Microsoft Server 2012 R2
SQL Server 2014 with mixed autentication mode build 12.0.2269
user connected: sa
procedure sp_send_dbmail sends an empty body mail when @query parameter is used.
---
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'INFO',
@recipients = <myemailaddress>,
@body = '',
@subject = 'subject',
@query = 'select idx, id, rtrim(articolo) as articolo, cast(rtrim(errore) as varchar(200))as errore from table';
---
obviously query works properly if executed.
thanks for Your help.
Mauro Branca
March 25, 2016 at 4:38 pm
try @body='your query here'
or create a variable and put the query result in @variable such that @body = @variable
or try: @attach_query_result_as_file = 0
I typically do something like:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<table border="1">' +
N'<tr><th>blah1</th><th>blah2</th>' +
N'<th>blah3</th></tr>' +
CAST ( ( SELECT td = blah1, '',
td = blah2, '',
td = blah3
from somewhere
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='peeps',
@subject = 'subject',
@body = @tableHTML,
@body_format = 'HTML' ;
March 26, 2016 at 6:19 pm
You have to pass @attach_query_result_as_file = 1 and can also pass @query_attachment_filename to set the name of the file if you want an attachment.
To send in the body, I use the same approach Jon posted. Add in some CSS and you can produce some pretty nice-looking emails if you want.
March 29, 2016 at 2:19 am
Hi Jon,
the HTML format works fine, while noone of other solutions works. In this case too the job worked fine in SQL server 2005 but have problems in SQL server 2014.
thanks
Mauro
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply