sp_send_dbmail succeding but sometimes failing

Also posted in Stack Overflow. Any feed back would be much appreciated.

I have a need for a SQL job to send multiple emails, and I have come across this error that I am unable to find any solutions for. When I send emails this way is succeeds every time I have tested.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = '*Valid Email Address*',
@body = 'test',
@subject = 'Testing';

When I add a query to the email occasionally the job will fail.

I have pulled out a snippet and simplified the SELECT query that replicates the issue.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = '*Valid Email Address*',
@body = 'test',
@query = 
'
SELECT GETDATE()
',
@subject = 'Testing';

When I run this it successfully sends me an email with the date time, if I run the code again I get the generic error, but not always.

Failed to initialize sqlcmd library with error number -2147467259.

I did some digging with SQL Server Profiler and came across this error message

The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

I am currently using a SA account but started with minimal viable privileges.

I have tried running the job under the generic email profile, with the same results.

The job that runs this code will succeed and fail for no apparent reason that I can find. There doesn't seem to be a pattern to the job running successfully or not either.

Has anyone come across this issue and managed to resolve it? Or is anyone able to point me in a direction to solve this?

Try setting @query_result_header to 0 and @query_no_truncate to 1.

@query_result_header = 1
@query_no_truncate = 0

query_result_header specifies whether the query results include column headers.

The ´query_result_header´ value is of type "bit". When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1 (and is only applicable if @query is specified).