msdb.dbo.sp_send_dbmail not queuing emails.

Kyle Abrahams
Kyle Abrahams used Ask the Experts™
on
Hi All,
I'm trying to troubleshoot msdb.dbo.sp_send_dbmail

Below is the procedure.  I know the profile works because I can send something without an attachment and it works fine.

The original select is from a global temp table.  (EG:  Select * from ##MyTempTable).

The select is then modified to append the blank column as we needed a different char for the CSV.

This was working up until last month, and trying to figure out what's going on.  I've tried changing the select to a different table but that's not working either.

the exec returns Null for the mailID, 1 for the RC, 0 for @@error.  

Any help greatly appreciated.  (SQL Server 2008 R2)

ALTER  procedure [dbo].[sendMail] 
@from varchar(max),
@fromDisplayName varchar(max),
@to varchar(max),
@cc varchar(max),
@bcc varchar(max),
@subject nvarchar(255),
@body nvarchar(max),
@AttachmentFileName nvarchar(255),
@select nvarchar(max),
@columnSeparator char(1) = '|'
as

set @columnSeparator = isnull(@columnSeparator, char(9))

declare @fromAdd varchar(max) = @fromDisplayName + ' <' + @from  + '>'

set @select =  'set nocount on; select '''' [sep=|' + CHAR(13) + CHAR(10) + 'BLANK],' + replace(@select, 'select', '')

declare @mailID int
declare @rc int

begin try
EXEC @rc = msdb.dbo.sp_send_dbmail
    @recipients= @to,
	@copy_recipients  = @cc,
	@blind_copy_recipients = @bcc,
    @body= @body,
    @subject = @subject,
    @profile_name = 'WorkingMailProfile',
	@from_address = @fromAdd,
	@query = @select,
	@attach_query_result_as_file = 1,
	@query_attachment_filename = @AttachmentFileName,
	@query_result_separator = @columnSeparator,
	@query_result_no_padding = 1,
	@exclude_query_output =1,
	@append_query_error = 0,
	@query_result_width =32767,
	@body_format = 'HTML',
	@mailitem_id = @mailID OUTPUT

	select @mailID, @rc, @@ERROR    -- RETURNS   NULL    / 1 / 0
  
end try
begin catch
selecT @@error test
end catch

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Try running a debug test  with @append_query_error = 1 and sending the email to just yourself.  That should(?) give you more error details (hopefully).
Kyle AbrahamsSenior .Net Developer

Author

Commented:
That yielded RC = 101
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
I'm pretty busy, it will take me a while to do this, you may be able to jump ahead of me, but at this point, I have:

1) listed the source code for sp_send_dbmail (EXEC sp_helptext 'sp_send_dbmail' and copy text to query window or "modify" that proc from SSMS)
2) a search for "RETURN 101" yielded nothing, so the 101 must be coming from a "RETURN @rc"
3) search through the called procs that send results using "RETURN @rc" and see which of them return an error code of 101 and why.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Kyle AbrahamsSenior .Net Developer

Author

Commented:
That looks like some help.

   EXECUTE AS CALLER  
        EXEC @rc = sp_RunMailQuery  

throws a 101, looking at the file size.
Kyle AbrahamsSenior .Net Developer

Author

Commented:
in runMailQuery:
    SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000)  

looks like the max is 100k?  Any way to override that?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Interesting.  I don't know off-hand of any way to override that. However, since the body allows up to nvarchar(max), you could try:

@attach_query_result_as_file = 0,

to include the results in the main body.  That's messy, I know, I try to avoid it, as it's much harder to read and use.

But, if that works, then you'll just have to decide if it's worth the effort to break the results into separate mail attachments each <= 100K, or to leave it all in the main body.
Kyle AbrahamsSenior .Net Developer

Author

Commented:
I took another (unsupported) approach.  I modified the sp_RunMailQuery to change the default to:

    SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000000)    
    

Open in new window


I'm now getting larger files but now my smtp server is blocking.  At least it's up the chain.  going foward with the number of updates it looks like I'll have to BCP this out to excel and send the link to the file.  But at least that solves my issues for now.

Thanks for the help, thought I was going crazy.
Kyle AbrahamsSenior .Net Developer

Author

Commented:
As always thanks for your knowledgeable support.  Led me in the right direction to find out the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today