locked
emails not being sent RRS feed

  • Question

  • Hello,

    We upgraded our database server from windows server 2008 r2 sp1 to windows server 2012 r2.
    I tested stored procedure is sending emails or not and it was sending.
    After that we did windows update.



    we were sending result of our stored procedure every morning.

    Now Next morning no one gets email.

    We are using below stored procedure to send a stored procedure result.

    EXEC CUS_sp_CreateNotificationSingleRecipient @FROM, @TO, 'EXTERNALEMAIL', @SUBJ, @MESSAGE, 'html','Y'

    Do you have any idea about this issue?

    DBMail is setup fine. I get test email from that. If we really needs for this procedure.

    Do you know any troubleshooting task that I have to follow to solve this issue?

    Do you ever had this kind of situation before?

    If you know solution of this situation that would be great.

    Thanks

    Virtual Reality

    Thursday, August 11, 2016 11:18 PM

Answers

  • Unfortunately, no one can help you.  This procedure doesn't actually send emails.  It just adds some rows to tables.  Something must be watching or polling these tables - most  likely, NotificationQueue.  And since you seem to have a custom-designed system, perhaps you should review the comments included in the procedure header and look in the documentation.

    For more information on configuring EX for notifications and setting up Client
    Application Services (formerly known as the Scheduler and Notification Service), please
    see the notification system topic in the Common Module online help.

    • Proposed as answer by Teige Gao Monday, August 15, 2016 1:28 AM
    • Marked as answer by VR16 Monday, August 15, 2016 2:08 PM
    Friday, August 12, 2016 6:29 PM
    Answerer

All replies

  • use msdb;

    EXEC sp_send_dbmail @profile_name='MyTestMail', @recipients='manishki@live.com', @subject='My Test Mail Service.', @body='Database Mail Received Successfully.'

    first test test mail.

    http://www.codeproject.com/Tips/846204/How-To-Send-Mail-Using-SQL-Server-Part..

    After sending test mail then try to send mail using store procedure.

    what is code use in store proc.

    share log of following.

    SELECT * FROM sysmail_mailitems
    GO
    SELECT * FROM sysmail_log
    GO
    select * from sysmail_log

    • Edited by AV111 Friday, August 12, 2016 6:36 AM
    • Proposed as answer by Teige Gao Friday, August 12, 2016 6:59 AM
    Friday, August 12, 2016 6:33 AM
  • Hello,

    I did above testing and these are my results

    

    I got email successfully.

    then I execute following queries:

    SELECT * FROM sysmail_mailitems where recipients like 'ripas@mesalands.edu'
    GO

    Then I ran 

    SELECT * FROM sysmail_log
    GO

    I can rans successfully CUS_sp_CreateNotificationSingleRecipient procedure but I am not getting any emails.

    What would be reason behind that?

    I am also attaching CUS_sp_CreateNotificationSingleRecipient procedure

    USE [dbname]
    GO
    /****** Object:  StoredProcedure [dbo].[CUS_sp_CreateNotificationSingleRecipient]    Script Date: 8/12/2016 11:01:25 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[CUS_sp_CreateNotificationSingleRecipient]
    
    /*
    This stored procedure demonstrates the creation of a custom notification for a single email 
    recipient.  This procedure can be used as a guide to see which tables are involved during 
    the creation of a notification.  It also shows how to properly insert data into these 
    tables.  For more information on configuring EX for notifications and setting up Client 
    Application Services (formerly known as the Scheduler and Notification Service), please 
    see the notification system topic in the Common Module online help.
    
    In order to insert into certain tables, you MUST use the seq_num_keys table to 
    generate the primary key.  Failure to do so will cause other process that insert into 
    these tables to fail.
    
    IMPORTANT: This stored procedure is NOT supported by Jenzabar.  Like any custom script, 
    please test on a non-production server.
    */
    
    /*
    Example stored procedure call:
    
    exec dbo.CUS_sp_CreateNotificationSingleRecipient 
    	'admin@school.edu', -- From address
    	'johndoe@school.edu', -- Recipient address (email address or EX user name)
    	'EXTERNALEMAIL', -- notification method code
    	'Test Subject', -- Subject
    	'Test Message', -- Message
    	'html', -- Format type
    	'Y' -- Include all recipients on single message?
    */
    	@from_email_address AS varchar(60),
    	@recipient_addr AS varchar(60),
    	@notification_method_cde AS varchar(15),
    	@subject as varchar(128),
    	@message as text,
    	@message_format as char(10),
    	@group_email_address as char(1)
    	
    AS
    
    DECLARE 
    	@from_address_id AS int,
    	@last_value AS int,
    	@increment_by AS int,
    	@maximum_value AS int,
    	@notification_id AS int,
    	@recipient_id AS int,
    	@notification_attempt_hist_seq_num AS int,
    	@error AS varchar(512),
    	@notification_method_def_id AS int,
    	@notification_queue_id AS int,
    	@notification_attempt_history_id AS int,
    	@group_email_address_id AS bit
    
    SET @notification_method_def_id = CASE @notification_method_cde 
    	WHEN 'APPALERT' THEN -1 
    	WHEN 'EXEMAIL' THEN -2
    	WHEN 'EXTERNALEMAIL' THEN -2
    END;
    
    IF @notification_method_def_id IS NULL
    BEGIN
    	SET @error = 'The @notification_method_cde parameter must be either APPALERT, EXEMAIL or EXTERNALEMAIL.';
    	GOTO EXIT_FAILURE;
    END;
    
    SET @group_email_address_id = CASE @group_email_address
    	WHEN 'Y' THEN 1
    	WHEN 'N' THEN 0
    END;
    
    IF @group_email_address_id IS NULL
    BEGIN
    	SET @error = 'The @group_email_address parameter must be either Y or N.';
    	GOTO EXIT_FAILURE;
    END;
    	
    -- Set the "from" email address.
    -- TODO: Uncomment line below and enter an email address.
    --SELECT @from_email_address = ''
    
    -- Set the notification method code.  Valid method codes are APPALERT, EXEMAIL and EXTERNALEMAIL.
    -- Note: If you want to send an APPALERT notification (the type of notification where users are
    -- alerted within the EX client application), the @recipient_addr variable should equal the
    -- USER_ID name in the APP_USER table.
    -- TODO: Uncomment the line below and enter a valid method code.
    --SELECT @notification_method_cde = ''  -- Can be either APPALERT, EXEMAIL or EXTERNALEMAIL
    
    -- Set the recipient address.  This can be an email address (for EXEMAIL and EXTERNALEMAIL 
    -- notification methods) or a user name (for APPALERT notification method).  This example
    -- only demonstrates a notification to a single recipient.  You can also create a notification
    -- that is sent to multiple recipients.  In the case of multiple recipients, you must create
    -- one notification_queue row and one notification_attempt_hist row for every recipient.
    -- TODO: Uncomment the line below and enter a recipient address (email address or EX user name)
    --SELECT @recipient_addr = '' -- For example, 'johndoe@school.edu' or 'TE_ADMIN'
    
    BEGIN TRAN
    
    -- Verify that the from_email_address exists in the FromAddress table.  If not, add a row.
    SELECT @from_address_id = IsNull(ID,0) FROM FromAddress WHERE FromEmailAddress = @from_email_address;
    IF @from_address_id IS NULL OR @from_address_id = 0
    BEGIN
    	SELECT 
    		@last_value = IsNull(last_value,0), @increment_by = IsNull(increment_by,1) , @maximum_value = IsNull(maximum_value,0)
    	FROM 
    		seq_num_keys WITH ( HOLDLOCK ) WHERE ref_id = 'FromAddress';
    	
    	SET @from_address_id = @last_value + @increment_by;
    
    	UPDATE 
    		seq_num_keys SET last_value = @from_address_id 
    	WHERE 
    		ref_id = 'FromAddress';
    
    	INSERT INTO FromAddress
    		( ID, DisplayName, ReplyToAddress, FromEmailAddress, ChangeUser, ChangeJob, ChangeTime ) 
    	VALUES 
    		( @from_address_id, @from_email_address, @from_email_address, @from_email_address, 'sa', 'Custom Notification', GetDate() );
    	SET @error = @@error;
    	IF @error <> 0 GOTO EXIT_FAILURE;
    
    END
    
    -- Add a row to the NotificationInformation table.  This row contains the actual message.
    -- Note: Only one NotificationInformation row should be created per notification.  One NotificationQueue
    -- row and one NotificationAttemptHistory row should be created for every recipient.
    SELECT 
    	@last_value = IsNull(last_value,0), @increment_by = IsNull(increment_by,1) , @maximum_value = IsNull(maximum_value,0)
    FROM 
    	seq_num_keys WITH ( HOLDLOCK ) WHERE ref_id = 'NotificationInformation';
    
    SET @notification_id = @last_value + @increment_by;
    
    UPDATE 
    	seq_num_keys SET last_value = @notification_id 
    WHERE 
    	ref_id = 'NotificationInformation';
    
    SET @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    INSERT INTO NotificationInformation(
    	ID, 
    	NotificationCategoryID, 
    	Subject, 
    	MessageInformation, 
    	MessageFormat, 
    	ExpirationDate, 
    	GroupEmailRecipients, 
    	PostedDate, 
    	FromAddressID, 
    	ChangeUser, 
    	ChangeJob, 
    	ChangeTime ) 
    VALUES 
    	( @notification_id, 
    	-3, -- Category ID; -3 is the "EX Client Email Service" from NotificationCategory
    	@subject, -- Subject
    	@message, -- Message text
    	@message_format, -- Message format (text or html)
    	'1900-01-01 00:00:00.000', -- Expiration date
    	@group_email_address_id,	-- Group email addresses (1 or 0); If 1 (Yes), the system will add all 
    			-- recipients to a single email message.  If 0 (No), a seaparete email 
    			-- message will be sent to each recipient
    	GetDate(), -- Posted date
    	@from_address_id, 
    	'sa', -- User Name
    	'Custom Notification', -- Job Name
    	GetDate() ); -- Job time
    	
    SET  @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    -- Verify that the intended recipient exists in the NotificationRecipient table.  
    -- If not, add a row.
    SELECT 
    	@recipient_id = IsNull(ID,0) FROM NotificationRecipient 
    WHERE
    	NotificationMethodDefID = @notification_method_def_id and 
    	RecipientAddress = @recipient_addr;
    
    IF @recipient_id IS NULL OR @recipient_id = 0
    BEGIN
    	SELECT 
    		@last_value = IsNull(last_value,0), @increment_by = IsNull(increment_by,1) , @maximum_value = IsNull(maximum_value,0)
    	FROM 
    		seq_num_keys WITH ( HOLDLOCK ) WHERE ref_id = 'NotificationRecipient';
    
    	SET @recipient_id = @last_value + @increment_by;
    
    	UPDATE 
    		seq_num_keys SET last_value = @recipient_id 
    	WHERE 
    		ref_id = 'NotificationRecipient';
    
    	SET @error = @@error;
    
    	IF @error <> 0 GOTO EXIT_FAILURE;
    
    	INSERT INTO NotificationRecipient (
    		ID, RecipientAddressFirstFour, NotificationMethodDefID, RecipientAddress,
    		ChangeUser, ChangeJob, ChangeTime )
    	VALUES ( @recipient_id, LEFT(@recipient_addr, 4), @notification_method_def_id, @recipient_addr,
    		'sa', 'Custom Notification', GetDate() );
    
    	SET @error = @@error;
    
    	IF @error <> 0 GOTO EXIT_FAILURE;
    END;
    
    -- Add a row to the NotificationAttemptHistory and NotificationQueue tables.
    -- Note: Create one NotificationQueue row and one NotificationAttemptHistory row 
    -- for every recipient.
    SELECT 
    	@last_value = IsNull(last_value,0), @increment_by = IsNull(increment_by,1) , @maximum_value = IsNull(maximum_value,0)
    FROM 
    	seq_num_keys WITH ( HOLDLOCK ) WHERE ref_id = 'NotificationAttemptHistory';
    
    SET @notification_attempt_history_id = @last_value + @increment_by;
    
    UPDATE 
    	seq_num_keys SET last_value = @notification_attempt_history_id 
    WHERE 
    	ref_id = 'NotificationAttemptHistory';
    
    SET @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    SELECT 
    	@notification_attempt_hist_seq_num = IsNull(Max(SequenceNumber),0) + 1
    FROM 
    	NotificationAttemptHistory
    WHERE 
    	ID = @notification_id and
    	NotificationRecipientID = @recipient_id;
    
    INSERT INTO NotificationAttemptHistory (
    	ID, NotificationInformationID, NotificationRecipientID, SequenceNumber, Status, AttemptDatetime, 
    	MailSenderID, ChangeUser, ChangeJob, ChangeTime )
    VALUES (
    	@notification_attempt_history_id, @notification_id, @recipient_id, @notification_attempt_hist_seq_num, 'Q', GetDate(),
    	-1, 'sa', 'Custom Notification', GetDate() );
    
    SET @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    SELECT 
    	@last_value = IsNull(last_value,0), @increment_by = IsNull(increment_by,1) , @maximum_value = IsNull(maximum_value,0)
    FROM 
    	seq_num_keys WITH ( HOLDLOCK ) WHERE ref_id = 'NotificationQueue';
    
    SET @notification_queue_id = @last_value + @increment_by;
    
    UPDATE 
    	seq_num_keys SET last_value = @notification_queue_id 
    WHERE 
    	ref_id = 'NotificationQueue';
    
    SET @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    INSERT INTO NotificationQueue(
    	ID, NotificationInformationID, NotificationRecipientID, SendingMethod, MailSenderID, ChangeUser, ChangeJob, ChangeTime )
    VALUES (
    	@notification_queue_id, @notification_id, @recipient_id, 'TO', -1, 'sa', 'Custom Notification', GetDate() );
    
    SET @error = @@error;
    
    IF @error <> 0 GOTO EXIT_FAILURE;
    
    EXIT_SUCCESS:
    	IF @@TRANCOUNT > 0
    	BEGIN
    		COMMIT TRAN;
    	END;
    	RETURN;
    
    EXIT_FAILURE:
    	SELECT @error
    	IF @@TRANCOUNT > 0
    	BEGIN
    		ROLLBACK TRAN;
    	END;
    	RETURN;


    Virtual Reality

    Friday, August 12, 2016 5:02 PM
  • Unfortunately, no one can help you.  This procedure doesn't actually send emails.  It just adds some rows to tables.  Something must be watching or polling these tables - most  likely, NotificationQueue.  And since you seem to have a custom-designed system, perhaps you should review the comments included in the procedure header and look in the documentation.

    For more information on configuring EX for notifications and setting up Client
    Application Services (formerly known as the Scheduler and Notification Service), please
    see the notification system topic in the Common Module online help.

    • Proposed as answer by Teige Gao Monday, August 15, 2016 1:28 AM
    • Marked as answer by VR16 Monday, August 15, 2016 2:08 PM
    Friday, August 12, 2016 6:29 PM
    Answerer
  • Hi VR16,

    Try to run following command and check if you are getting mail.



    • Edited by AV111 Sunday, August 14, 2016 7:16 AM
    • Proposed as answer by Teige Gao Monday, August 15, 2016 1:28 AM
    Sunday, August 14, 2016 7:14 AM
  • Thank you very much for your help and gives right direction to solve this issue. I  followed steps of configuring EX for notifications and setting up Client 
    Application Services (formerly known as the Scheduler and Notification Service), and I found that I have to restart one client application service(related to Jenzabar software) after that I am getting all previous and current emails that were in queue.

    Thanks


    Virtual Reality

    Monday, August 15, 2016 2:07 PM