Solved

sql mail - duplicate emails

Posted on 2012-04-04
8
458 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
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.

 
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
 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solution for warm standby SQL server 20 50
SQL Statement to Update Email Domain 2 30
Count with a subquery showing details 10 44
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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