How to send json query as attachment using sp_send_dbmail

Question

Is it possible to export the data from a column into json and email?

Col is NVARCHAR(MAX) storing entire json string.

EXEC msdb.dbo.sp_send_dbmail  
  @profile_name = 'Profile',
  @recipients = '[email protected]',
  @subject = @Subject,
  @body = @Body,  
  @query = 'SELECT TOP(1) [col] FROM [dbo].[tbl] ORDER BY [Date] DESC;',
  @execute_query_database = 'DBName',
  @attach_query_result_as_file = 1,
  @query_attachment_filename = 'Report.json';

Executing produces error:

Failed to initialize sqlcmd library with error number -2147417850.

Answer 1

Yes, that's possible. Seems like you're missing the required permissions (Executing sp_send_dbmail with an Agent Job: Failed to initialize sqlcmd library). And the @recipients parameter.