?
Solved

sql mail - duplicate emails

Posted on 2012-04-04
8
Medium Priority
?
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

718 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