sp_send_dbmail fails with attachment

Question

I need to send email with file attachments. The attachment has to come from image type data field. When running the following code in both a query window and also stored procedure, no email is sent. in query window, it just says 'command(s) completed successfully', but no email.

EXEC  msdb.dbo.sp_send_dbmail @recipients = '[email protected]',
    @subject = 'test',
    @execute_query_database = 'myDB',
    @body = 'test',
    @body_format = 'HTML', 
    @profile_name = 'myProfile',
    @append_query_error = 1,
    @query = 'Select docData from [myDB].[dbo].[Documents] Where id = 1',
    @query_result_header = 0,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Test.doc',
    @exclude_query_output = 1,
    @query_no_truncate = 1;

The email is send when I remove @query:

EXEC  msdb.dbo.sp_send_dbmail @recipients = '[email protected]',
    @subject = 'test',
    @execute_query_database = 'myDB',
    @body = 'test',
    @body_format = 'HTML', 
    @profile_name = 'myProfile';

But I need the attachments code to work. Any help would be greatly appreciated.

Answer 1

Too long to comment.

Change @exclude_query_output = 0 to @exclude_query_output = 1. This will likely print a message (error message) on what is going on.

In your example, I was able to get around the error:

Failed to initialize sqlcmd library with error number -2147024809.

that I got and I expect you'll likely get by changing @query_result_header = 0 to @query_result_header = 1. I'm not sure why the lack of headers is causing it to fail, but it fixed it on my dev box.

Answer 2

In my case instead of this:

SET @Send_Query_Text = 
'SELECT Message_Text FROM DataMonitoring.Alert_Message WHERE Message_Setup_Key =' + CAST( @Message_Setup_Key AS VARCHAR(10)) + ' AND Mailing_GUID = ''' + CAST(@Mailing_GUID AS VARCHAR(36))+'''';

I should've done this:

SET @Send_Query_Text = 
    'SELECT Message_Text FROM Staging_Area.DataMonitoring.Alert_Message WHERE Message_Setup_Key =' + CAST( @Message_Setup_Key AS VARCHAR(10)) + ' AND Mailing_GUID = ''' + CAST(@Mailing_GUID AS VARCHAR(36))+'''';

As you can see, the query should contain the database, otherwise it won't work

Answer 3

I had the same issue but none of the solutions I found helped. Finally MSSQLTips led me into the right direction. Running profiler I got the following error message:

Could not obtain information about Windows NT group/user 'DOMAIN\user', error code 0x5

Checking the windows security event log for Audit Failures I finally found the reason why:

The specified account's password has expired.

The active directory account which SQL Server and SQL Server Agent ran with had been set up to allow password expiration. Disabling password expiration on the AD account immediately resolved the issue.

Answer 4

I'll throw in another option here. I tried the suggestions above to no avail. After taking my script apart, I found that I was missing a prefix in my SQL. Instead of a Syntax error I received the same error as everyone else. Once I fixed the SQL, it worked.