fwstealer
asked on
sql mail - duplicate emails
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_ha ndle,
@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=@Dl gHandle AND RQ.status=3;
INSERT INTO dbo.ErrorLog (RequestMessage,ErrorMessa ge,Timesta mp)
SELECT @Request,@Msg,@startTime FROM InitiatorSQueue RQ
WHERE RQ.conversation_handle=@Dl gHandle AND RQ.status=3;
--Build the subject and message body here
SET @subject = N'Request error in PF submission at: ' +CONVERT(VARCHAR(50),@star tTime)
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
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_ha
@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=@Dl
INSERT INTO dbo.ErrorLog (RequestMessage,ErrorMessa
SELECT @Request,@Msg,@startTime FROM InitiatorSQueue RQ
WHERE RQ.conversation_handle=@Dl
--Build the subject and message body here
SET @subject = N'Request error in PF submission at: ' +CONVERT(VARCHAR(50),@star
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()'
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
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...
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...
ASKER
not sure how to turn on the profile and trace.
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
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
ASKER
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?
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?
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_mailite ms 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!
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_mailite
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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