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.)

  • One way would be to use PowerShell to run the query and store the result in a variable, which could then be used as the body of an email sent by PowerShell (Send-MailMessage), or send the result to a file and attach the file to the email. – Tony Hinkle Oct 25 '17 at 1:02
  • @TonyHinkle Thanks! OP enhanced. Would strongly prefer to keep within sql. – Jonesome Reinstate Monica Oct 25 '17 at 4:54
  • Be aware that certain editions of SQL 2012/2014 have extra issues with sp_send_dbmail. So let’s solve the problem together by realizing that certain combinations of the parameters and content for this stored procedure cause downstream errors. – clifton_h Oct 25 '17 at 5:33
  • And yes, there is another method: it’s called CLR. Don’t even bother with the OA_methods as they are unstable (let’s just say memory corruption is probable) and actually can block sp_send_dbmail from working in the same batch! For example. :/ though I am on your side about using T-SQL, as returning actual cached plans can be a lot of data itself and I was able to do this. :) – clifton_h Oct 25 '17 at 5:37
  • 1
    Am I right in thinking that the system is complaining about not being able to send an email > 2GB? – Vérace Oct 25 '17 at 5:55

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.

System parameters in Database Configuration

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.