Using both Query and Body in sp_send_dbmail

Transact-SQL
1

Hi,

We are facing the issue while using the HTML format, variable which is in VARCHAR(MAX) datatype cannot hold more data to display in the mail content. Its getting truncated.

Is it possible to use both @Query and @body in sp_send_dbmail like below

EXEC msdb.dbo.sp_send_dbmail @recipients='ms@gmail.com',
@subject = @ENVIR,
@body = 'Hi',

@body='Please find the details below',

@query='SELECT * FROM TABLE',
@body_format = 'HTML';

Please let us know your input on this. Thanks in advance

2

You can have both @body and @query. Try it

3

Hi gbritton,

Its not working. Is there any datatype which can hold more data other than VARCHAR(MAX).

4

nope. but that's probably not the problem. that is unless you are trying to build emails > 2G in size! (Please tell me you are not trying to do that!)

Please elaborate on what, specifically, is not working.

Note: sp_send_dbmail uses NVARCHAR, not VARCHAR so the max size in characters is about 1GB