Sending attachment with sp_send_dbmail getting error Failed to initialize sqlcmd library with error number -2147467259

Question

I am trying to test sending an attachment in SQL with the code below. I get an error -Failed to initialize sqlcmd library with error number -2147467259. Sending emails works just fine, only when adding the @query do I start getting this error. What am I missing?

DECLARE @query nvarchar(max) = 'select top (1) * from employees';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'me@gmail.com',
@query = @query,                     
@query_attachment_filename = 'employees.csv',      
@body_format = 'HTML',
@subject = 'Test';

Answer 1

Queries run using the @query parameter for sp_send_dbmail are run in the context of msdb. This means that for your query, you are effectively trying to reference an object msdb.dbo.employees. This object is unlikely to exist in msdb, as it is a system database (and if it does, I would suggest moving it), and as the object doesn't exist the query fails to parse and sp_send_dbmail generates an error.

To fix the problem, use 3 part naming in the query you define for the @query parameter:

DECLARE @query nvarchar(MAX) = N'SELECT TOP (1) * FROM YOurDatabase.dbo.employees ORDER BY YourColumn;'; --Replace values as needed
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'me@gmail.com',
@query = @query,                     
@query_attachment_filename = 'employees.csv',      
@body_format = 'HTML',
@subject = 'Test';