Solved

sql mail - duplicate emails

Posted on 2012-04-04
8
455 Views
Last Modified: 2012-04-04
I've got a stored proc that sends an email on error. this works but i seem to be getting duplicate emails and I'm not sure why. any suggestions?


USE [Prnt]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InitiatorSQueueProc]
WITH EXECUTE AS N'dbo'
AS

DECLARE @isDebug BIT = 0
DECLARE @startTime as datetime2 = GetUTCDate()

DECLARE @profile_name NVARCHAR(128)='it-support'

DECLARE @recipients NVARCHAR(255) ='pts@company.com'
DECLARE @blind_copy_recipients NVARCHAR(255) ='ts@daje.com'

DECLARE @subject NVARCHAR(128)
DECLARE @body NVARCHAR(4000)
DECLARE @DlgHandle UNIQUEIDENTIFIER;
DECLARE @Msg xml;
DECLARE @Request xml;
DECLARE @MsgType sysname;
WHILE(1=1)
BEGIN
BEGIN TRANSACTION;

WAITFOR
(
RECEIVE TOP(1)
    @DlgHandle=conversation_handle,
    @Msg=message_body,
    @MsgType=message_type_name
  FROM InitiatorSQueue),TIMEOUT 1000;

IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END
IF @MsgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
      SELECT @Request=RQ.message_body FROM InitiatorSQueue RQ
            WHERE RQ.conversation_handle=@DlgHandle AND RQ.status=3;
      
      INSERT INTO dbo.ErrorLog (RequestMessage,ErrorMessage,Timestamp)
            SELECT @Request,@Msg,@startTime  FROM InitiatorSQueue RQ
            WHERE RQ.conversation_handle=@DlgHandle AND RQ.status=3;
      
--Build the subject and message body here
SET @subject = N'Request error in PF submission at: ' +CONVERT(VARCHAR(50),@startTime)
SET @body = N'Request:'+char(13) + char(10)+CAST(@Request as varchar(MAX)) + char(13)+char(10) + 'Error: '+CAST(@Msg.query('
declare default element namespace "http://schemas.microsoft.com/SQL/ServiceBroker/Error";
/Error/Description/text()') as varchar(MAX));

IF (@isDebug = 0) -- for debugging
      BEGIN --
            EXEC  msdb.dbo.sp_send_dbmail
            @profile_name,
            @recipients,
            NULL,
            @blind_copy_recipients,
            @subject,
            @body    
                     
      END
ELSE
    BEGIN
            Print 'Using Profile: ' +  @profile_name
            Print 'TO: ' +  @recipients
            Print 'BCC: ' + @blind_copy_recipients
            Print 'Subject: ' + @subject
            Print 'Body: ' + @body
           
    END        
      
END
END CONVERSATION @DlgHandle;
COMMIT TRANSACTION
END

GO
0
Comment
Question by:fwstealer
  • 5
  • 3
8 Comments
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37805851
Turn on the profile and trace what is actually happening. The sympton sounds as if you are doing the send mail followed by the rollback rather than the code you inserted above...
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37805861
That did not come out right...  What I meant is taht syntatically, the code above looked good to me , but is not matching the symptom you described.  So I'd need to see the profiler trace to understand what i am missing...
0
 

Author Comment

by:fwstealer
ID: 37805925
not sure how to turn on the profile and trace.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37806053
Start - All programs - Micrsoft Sql Server 2008 - Performance Tools - Sql Server profiler

File - New Trace

Then connect in as a user with sa rights

It will bring up a default Trace Properties window

Click on Events Selection

I've attached a sample word doc showing my trace everything events selection and a filter to just capture the activity for the procedure you showed me above.  You will see what looks like duplicate info. You need to keep in mind that I have it set to show me the remote procedure starting , the statement starting, the statement ending and the remote procedure ending so you see the same statement multiple times as it goes through its processing.

But when something unusual is going on, I find this to be the best thing going.  It tells me what IS happening rather than my guessing and possibly changing somehting this is not a problem.
Trace-Sample.docx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:fwstealer
ID: 37806252
1. comment this out:
IF (@isDebug = 0) -- for debugging
      BEGIN --
            EXEC  msdb.dbo.sp_send_dbmail
            @profile_name,
            @recipients,
            NULL,
            @blind_copy_recipients,
            @subject,
            @body    
                     
      END
ELSE
    BEGIN
            Print 'Using Profile: ' +  @profile_name
            Print 'TO: ' +  @recipients
            Print 'BCC: ' + @blind_copy_recipients
            Print 'Subject: ' + @subject
            Print 'Body: ' + @body
           
    END        

2. drop a bad file in a folder for processing the email isn't sent; however, when the above is not commented out it sends an email as supposed to but then a minute later it sends the exact same email.

i'm looking at the trace file - ur doc helped me. any ideas why it sends the dups?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37806483
As you say the second e-mail shows up a minute later and did not say you saw another call to the sendmail routine.  So it looks like your procedure functioned correctly and the sendmail routine must be causing the duplicates....

What do you see when you look at the database mail log. (Right click on Management , right click Database Mail, Right Click on View Database Mail)

Are you seeing errors here?

if you execute

SELECT TOP 10 *  FROM [msdb].dbo.sysmail_mailitems with (nolock)
order by send_request_date desc

Do you see only one or two messages and what is the status?

And you have my apologies. I should probably have sent you here first!
0
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
ID: 37806597
And please recheck recipients and blind copy list to make sure you were not accidentally included in an extra distribution list that caused you to receive two copies.  :-)
0
 

Author Comment

by:fwstealer
ID: 37806688
think i have it resolved - external email was blocked and had to get a forwarder setup; now that i point to the forwarder it seems to be working just fine; thanks for all of your help as it gave a tons of new insight
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now