sp_send_dbmail executes query but not sending the email - error
-
Hi All,
I am facing this strange issue when we try to send an email which includes a query. I am using the following syntax:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestMailProfile',
@recipients = '<valid_email>',
@subject = 'Email Attachement Check',
@body = 'Sample Body Text',
@body_format = 'HTML',
@execute_query_database = 'master',
@query = N'SELECT GETDATE() AS CurrentDate;',
@attach_query_result_as_file = 1,
@exclude_query_output = 1,
@query_attachment_filename = 'Attachment.txt'The query is resulting "Command(s) completed successfully." but it doesn't queue up the email. Hence I went through the system procedures and found out that the failure occurs in side the system stored procedure "sp_RunMailQuery" in msdb database.
EXEC @rc = master..xp_sysmail_format_query
@query = @query,
@message = @mailDbName,
@subject = @uidStr,
@dbuse = @dbuse,
@attachments = @query_attachment_filename,
@attach_results = @attach_res_int,
-- format params
@separator = @separator,
@no_header = @no_header_int,
@no_output = @no_output_int,
@echo_error = @echo_error_int,
@max_attachment_size = @fileSize,
@width = @width,
@query_no_truncate = @query_no_truncate_int,
@query_result_no_padding = @query_result_no_padding_int
RETURN @rcIt works fine if we don't use @query. The SQL Server Agent is running under Local System Account.
Any advice in order to resolve this issue is highly appreciated.--------
Manjuke
http://www.manjuke.com -
Not sure what's wrong. After changing the email to my email and commenting out the profile (I just use the default), it worked exactly as expected.
Hmmm... maybe the profile you setup doesn't work for attachments for some reason.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I changed the query to like this: (@exclude_query_output = 0)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestMailProfile',
@recipients = '<valid_email>',
@subject = 'Email Attachement Check',
@body = 'Sample Body Text',
@body_format = 'HTML',
@execute_query_database = 'master',
@query = N'SELECT GETDATE() AS CurrentDate;',
@attach_query_result_as_file = 1,
@exclude_query_output = 0,
@query_attachment_filename = 'Attachment.txt'And it threw this error :
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517 [Batch Start Line 0]
Query execution failed: ?Msg 15404, Level 16, State 19, Server AS-ASC-GIDDEV\SQLSERVER2012, Line 1
Could not obtain information about Windows NT group/user 'TEISAN\manjuke.fernando', error code 0x5.--------
Manjuke
http://www.manjuke.com -
Again, I have to comment out the profile and use my default profile but when I change the email address to my own, I get the expected email with the expected attachment and no errors. I'm really starting to think there's an error in the profile you setup. And I'm also using SQL Server 2012.
What SP/CU are you using in 2012?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for the help. After doing few experiments I managed to fix the issue. The issue wasn't in the mail profile and this is what I have done.I have created a user login in SQL Server (eg: WebEmailUser) with public server role.
And under user mapping for the database which I am executing the query I gave 'public' & 'db_datareader' database roles.
Under msdb database I have included the 'DatabaseMailUserRole'.
When I am executing the procedure I execute it as WebEmailUser.
EXECUTE AS LOGIN = 'WebEmailUser'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestMailProfile',
@recipients = '<valid_email>',
@subject = 'Email Attachement Check',
@body = 'Sample Body Text',
@body_format = 'HTML',
@query = N'SELECT GETDATE() AS CurrentDate;',
@execute_query_database = 'master',
@attach_query_result_as_file = 1,
@exclude_query_output = 0,
@query_attachment_filename = 'Attachment.txt'Then I was able to fix my problem. 😀
Mail (Id: 13556) queued.
--------
Manjuke
http://www.manjuke.com -
Very cool feedback. Thanks for taking the time. I would never have come up with that because I thought you were operating with a "sysadmin" privs. Bad assumption on my part.
If you have the new user execute stored procedures as part of the query that creates an attachment, you might want to create a "db_Executor" role in the appropriate database(s) and grant it that role.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff,
I will consider this:
"If you have the new user execute stored procedures as part of the query that creates an attachment, you might want to create a "db_Executor" role in the appropriate database(s) and grant it that role. "
--------
Manjuke
http://www.manjuke.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply