using msdb.dbo.sp_send_dbmail with @query parameter error in trigger

Question

I've an after DELETE TRIGGER that wants to send an email.

All is working fine except when I try to add the @query parameter to the msdb.dbo.sp_send_dbmail.

My scope is like this:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'myprfilename',
    @recipients = 'listofrecipients',
    @subject = 'test trigger email',
    @query = 'select * from DELETED'

and the error message is:

Msg 22050, Level 16, State 1, Line 3 Failed to initialize sqlcmd library with error number -2147467259.

If I remove the @query parameter, the email is sent correcly. Any idea of how resolve my issue?

The production server is SQL 64bit version 12.0.2000.8

Answer 1

check what account runs SQL Server Service in SQL Configuration Manager. Changing from "Local Service" to "Local System" solves my problem.