sp_send_dbmail solution for attachments

When using the @query parameter with sp_send_dbmail via the SQL Agent to send an attachment in the email you may get the following error:

“Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.”

Apparently this is the sp_send_dbmail equivalent of the nearly useless Visual Basic “Object variable or With block variable not set.” error.

Note that the syntax was correct and everything worked fine when operating from a query session.

One way to get around it is to preface the execution context with a power user:

EXECUTE AS LOGIN = 'ElmerFudd'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alert Stuff',

@recipients = 'bugz@bunny.org; ',

@subject = 'Favorite vacations',

@query = N'

use msdb;

go

select top 10 ...

This took quite an effort to solve so I decided to share …

2¢