Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql mail - duplicate emails

Posted on 2012-04-04
8
Medium Priority
?
487 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

877 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