SQL Server 2012 and 2014
I can make sp_send_dbmail work fine when my SQL query is compact.
When the query itself is a big long beast, I can't make sp_send_dbmail work. (The deadly "
Failed to initialize sqlcmd library with error number -2147467259.")
The question is: Within SQL, can I use a different approach to send query results via email, to use in a SQL Agent job??
(IOW: Is there an approach OTHER than sp_send_dbmail + @attach_query_result_as_file)
Is it possible to build the data set to email in Step 1 of a job, and then in Step 2 take the result from Step 1 and attach it to an email?
Ideally, it would be possible to break the process up into discrete steps. The "single shot" sp_send_dbmail is fragile. If I could build the payload in one step, and pass that payload to the email in the next step, things would be much more managable. (And if that attachment could be zipped, oh the joy! And if it could be an xls, we could get some work done!)
(I just spent half a day fighting with sp_send_dbmail. The solution, after hours of obscure errors, was to package the big query in a view, so the sp_send_dbmail query is just three lines. But this is crazy coupling. sp_send_dbmail itself is the problem. We need a different path.)
Best Answer
That might be the case of checking the attachment limit size set in your mail server, or the Maximum File Size (Bytes) setting in the database mail configuration. The default value for this setting is 1000000(bytes), if your result set is larger than this value it won't be sent.