2

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'email1@web.com;email2@web.com;email3@web.com' 
    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@web.com',
    @blind_copy_recipients = 'email@web.com',
    @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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.