April 11, 2011 at 5:40 am
hello to all
i having a problem on sending email through sqlmail script/stored procedure because it repeat sending mail twice or even more with same content. coz in my application i already put outside the loop this sqlmail but still they recieve more email how can i prevent to send more email from the recipient. please help.
thanks
April 11, 2011 at 6:12 am
sp_sendmail doesn't auto-magically break and double send, so there's got to be something unexpected in the workflow instead.
Are you looping thru a table or results, and calling sp_sendmail on each row? does your command feature DISTINCT email to make sure you don't get duplicates?
help us help you and show us the code you are using to send the mail
Lowell
April 11, 2011 at 10:30 pm
this is my stored proc;
ALTER proc [dbo].[spu_Send_Email_Notification]
@From varchar(max),
@To varchar(max),
@cc varchar(max),
@Subject varchar(max),
@Body varchar(max)
as
EXEC msdb.dbo.sp_Send_dbmail
@profile_name ='myclick',
@from_address =@From,
@recipients =@To,
@copy_recipients=@CC,
@subject =@Subject,
@body =@Body
its gonna wierd coz when we try to use the client email(domain email) add and executed thru my application they recieve only one email while we try to use yahoo and gmail account the user recieve 3 or 4 with same content. i read the result from the tables to get the selected person to the recipient of the email, here is my stored proc reading the rows in the tables.
-- spu_CHS_SendEmail_AllResources '00003I','ENS -CR-2010-000102'
ALTER proc [dbo].[spu_CHS_SendEmail_AllResources]
@fxPersonnelkey VARCHAR(7),
@fxCRequestKey varchar(21)
as
declare @alertcode varchar(max)
set @alertcode =(select fcMessage from dbo.tblAlerts where fcAlertCode='000015')
SELECT
fcContactName,
fcEmail,
@alertcode
FROM dbo.tblChangeRequestResources
WHERE fxPersonnelkey=@fxPersonnelkey and fxCRequestKey =@fxCRequestKey and fbCheck = 1
and here is my method in my application.
protected void SendEmailtoSelectedResources(string keyowner,string crid)
{
SqlDataReader rd;
rd = SqlHelper.ExecuteReader(gConn.SQLConnection, CommandType.StoredProcedure, "spu_CHS_SendEmail_AllResources",
new SqlParameter("@fxPersonnelkey", keyowner),
new SqlParameter("@fxCRequestKey", crid));
try
{
string subjt = "Change Request ID:" + " " + this.lblChangeRID.Text;
if(rd.Read())
{
Session["emailadd"] = (string)rd[1].ToString().Trim();
Session["message"] = (string)rd[2].ToString().Trim();
}
//this is where email execute
SendCRDetails.SendMailcc("ibos@megalink.ph", (string)Session["emailadd"].ToString().Trim(), "", subjt, (string)Session["message"].ToString().Trim());
}
catch (Exception ex)
{
string msg = ex.Message;
msg = ex.InnerException.Message;
Response.Redirect("../CustomErrorPage.aspx?ErrorMessage=" + msg);
}
}
hope you could help me..
thanks
April 12, 2011 at 1:42 am
pretty straightforward code; I'm still guessing that your table dbo.tblChangeRequestResources has multiple emails for the same person, so i'd suggest adding a DISTINCT clause to your procedure:
-- spu_CHS_SendEmail_AllResources '00003I','ENS -CR-2010-000102'
ALTER proc [dbo].[spu_CHS_SendEmail_AllResources]
@fxPersonnelkey VARCHAR(7),
@fxCRequestKey varchar(21)
as
declare @alertcode varchar(max)
set @alertcode =(select fcMessage from dbo.tblAlerts where fcAlertCode='000015')
SELECT DISTINCT
fcContactName,
fcEmail,
@alertcode
FROM dbo.tblChangeRequestResources
WHERE fxPersonnelkey=@fxPersonnelkey and fxCRequestKey =@fxCRequestKey and fbCheck = 1
double check your source table and see if this returns any results...if it does, that's the emaisl that could potentially get multiple emails:
SELECT
fcEmail ,
COUNT(fcEmail)
FROM dbo.tblChangeRequestResources
GROUP BY fcEmail
HAVING COUNT(fcEmail) > 1
Lowell
April 12, 2011 at 2:05 am
why are you bothering to offload this through SQL Server? Send mail directly from the app straight to the SMTP server using System.Net.Mail class, there is a MailMessage() object there that you may exploit and you don't need to involve SQL Server in all this, it has better things to do than manage mail profiles
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2011 at 10:25 pm
thanks lowell i'll give it a try..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply