SQL job is completing without sending email to configured recipients

Question

I have an SQL job completing without sending email to configured recipients. Here is the stored procedure I am using. Names changed accordingly. All other mail is getting sent via db mail through a stored procedure. I believe there is a size limit on the mail send.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spReport]    Script Date: 3/3/2016 3:29:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      J Romero
-- Create date: 10/21/2015
-- Description: Procedure to email a list of Subscribers for the last 12 months
-- =============================================
ALTER PROCEDURE [dbo].[spReport]

AS
BEGIN
    SET NOCOUNT ON;
    dECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
    declare @TableHTML nvarchar(max)
    SET @p_recipients = N'[email protected];[email protected];[email protected]' 
    set @TableHTML =
    N'<H1>eMail Subscribers-Last 12 Months</H1>' +
    N'<table border="1">' +
    N'<tr><th>eMail Address</th><th>Date Subscribed</th></tr>' +
    Cast ((select td = email,      '',
                    td = Date_Subscribed
    FROM email_subscriptions
    where date_subscribed > dateadd(D,-365,getdate())  
    ORDER BY date_subscribed
    for xml path('tr'), type) as nvarchar(max)) + N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Admin', 
    @recipients = @p_recipients,
    @reply_to = '[email protected]',
    @blind_copy_recipients = '[email protected]',
    @subject = 'Subscribers-Last 12 Months',
    @body = @TableHTML,
    @body_format = 'HTML'
END

Every other Procedure is sending mail, and no errors are coming up in logs. Ok, Mail profile has been enabled. I have found that there is a size limit to the emails you can send from db. Is there a setting to change this?

Answer 1

The issue was the size limitation. We just made the file smaller using a shorter date range. Also changed size limit through sql function in case we wanted to select a larger date range later.

Reference: Database Mail Stored Procedures (Transact-SQL)

Particularly:

• sysmail_help_configure_sp
• sysmail_help_profile_sp
• sysmail_help_account_sp
• sysmail_help_principalprofile_sp
• sysmail_help_profileaccount_sp