sp_send_dbmail fails with attachment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

sp_send_dbmail fails with attachment

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-15 : 10:56:59

Hi, I have two procedures which send emails, one with no attachment, the other with an attachment. The No Attachment works fine - I use it to notify if a step in a job fails. The with attachment works when ran as a Proc, but the same statement fails inside a Job saying:
"Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters"
When this fails I get an automated email (using the no attachment proc)
saying it failed. Here is SQL of both:

CREATE PROCEDURE [dbo].[WithAttachment] @Body varchar (max), @Subject varchar(max)
,@QueryAttachment varchar(max),@DB varchar(50),@AttachmentName varchar(250)
AS

EXEC msdb.dbo.sp_send_dbmail
@recipients ='me@me.com',
@body = @Body,
@subject = @Subject,
@profile_name = 'myDBMailProfile',
@query = @QueryAttachment,
@execute_query_database = @DB,

@attach_query_result_as_file = 1,
@query_attachment_filename = @AttachmentName,
@body_format = 'TEXT';

----------------------------

CREATE PROCEDURE [dbo].[NoAttachment] @Body varchar (max), @Subject varchar(max)
AS

CREATE PROCEDURE [dbo].[NoAttachment] @Body varchar (max), @Subject varchar(max)
AS

EXEC msdb.dbo.sp_send_dbmail
@recipients ='me@me.com',
@body = @Body,
@subject = @Subject,
@profile_name = 'myDBMailProfile',
@importance = 'high',
@body_format = 'HTML';
GO

-----
Since noattachment proc works, mail is working. Since the withattachment proc works when the SQL from Job is ran from query window, the SQL is fine. I've given NetworkUser permission to the query the attachment is based on.
Anyone any other ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 11:08:24

how are you calling the procedure? can you show calling query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-15 : 11:38:17

Here is call:
exec [WithAttachment]
'test body2'
,'test subject2'
,'SELECT top 1 * FROM dbName.dbo.mytable'
,'dbName'
,'test attachment.txt'

It runs fine from query window but fails inside job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources