1

I'm trying to send an email using sp_send_dbmail having my select coming from an XML variable, but I'm getting the following error:

Msg 22050, Level 16, State 1, Line 6
Failed to initialize sqlcmd library with error number -2147467259.

This is my query:

execute as login = 'user' 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'xpto', 
@Subject = 'Report', 
@recipients = 'email@email.com',
@body_format = 'HTML',
@body = 'Test',
@attach_query_result_as_file = 1, 
@query_result_separator = ',',
@query_result_header = 0,
@query_result_no_padding = 1,
@query = 'SELECT @signal_xml = isnull(@ContentsFile,'')',
@query_attachment_filename = 'test.csv'

Is this possible? I'm trying to create a CSV file without the extra lines SSRS adds on the end of the files.

Thank you so much for you help! Any tip is pretty much appreciated!

jarlh
  • 42,561
  • 8
  • 45
  • 63
Evy
  • 11
  • 1
  • `SELECT @signal_xml = isnull(@ContentsFile,'')`, which in the context of a batch just assigns a value to the variable, isn't going to be a valid query for `sp_send_dbmail`. The query needs to return a result set. – squillman Aug 10 '22 at 21:15
  • 2
    1) Don't try and send emails via SQL Server - totally wrong place to do it. 2) Just convert the data before you call the sp_send. – Dale K Aug 10 '22 at 21:16
  • For what it's worth, `-2147467259` is `0x80004005`, which is `E_FAIL`. In other words, it's helpfully telling you that something went wrong. This hopefully demonstrates why the mail functionality in SQL Server is less than ideal from a maintenance perspective; using it for anything more complicated than simple alerts is a bad idea. – Jeroen Mostert Aug 11 '22 at 09:33

0 Answers0