Solved

sql mail - duplicate emails

Posted on 2012-04-04
8
452 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

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, …
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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