'sp_send_dbmail fails with attachment

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.

sql-serversql-server-2012


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

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.

Solution 4:[4]

Ensure your query is in the format:

[Database].[Schema].[Table] name

this fixed it for me

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 zztxcxc
Solution 2 Sebastian H.
Solution 3
Solution 4 j__carlson

Related Questions