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?