SQL Mail

Question

Post reply

SQL Mail

Vikas-198293

SSC Enthusiast

Points: 113
More actions
March 21, 2005 at 7:05 am

#60551

Hi,
I am having application of newsletter mailing from website. From that I am sending around 100k Mail using SQL mail + Persits Component (using ASMTP). My mail Body is stored in a table and its less then 8k. In mail body I am replacing few details from the contact details (The person who is going to receive the newsletter) using replace function. We are sending text/Html mails
Now my problem is few of my mails are going out, without subject, few without body and few with partial body and around 50-60% are ok. So what is the possible cause for this and how it can be over come?
Thanks
Vikas

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

just my 2ct :

- having NULLs for replacement - values ?

- or using case-expressions without an ELSE pitfall ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Vikas-198293 SSC Enthusiast Points: 113 More actions · Answer 2

Hi,

Yes, All null's are handled (using isnull), So no chance of Null.

No there is no case-expressions

Part of Stored Proc

--**---

insert into #tmpschedule select top 1000 sch_Id,sch_Cntid,sch_Emailid,sch_Groupid,sch_Userid,sch_type from tbl_Scheduleletter where sch_Senddate <= getdate() and sch_Send = 0

set @totalletter = (select max(ts_id) from #tmpschedule)

set @cntletter = 1

EXEC @mail = sp_OACreate 'Persits.MailSender', @MailID OUT

EXEC @mail = sp_OASetProperty @MailID, 'Host', 'mail.domain.com'

while (@cntletter <= @totalletter)

begin

set @Tcode = rand()*1000000000000

select @schid = ts_sch_Id , @ccntid = ts_sch_Cntid , @emailid = ts_sch_Emailid , @groupid = ts_sch_Groupid , @custid = ts_sch_Userid , @Restart = ts_sch_type from #tmpschedule where ts_id = @cntletter

select @Useremail = isnull(Email,'') , @Userfname = isnull(FirstName,'') , @Userlname = isnull(LastName,'') from Users where UserName = @custid

set @Userfullname = @Userfname + ' ' + @Userlname

select @emailbody = isnull(convert(varchar(8000),ceEmailContent),'') , @emailsubject = isnull(ceEmailSubject,'') , @Order = ceOrder from tblCampaignEmails where ceCampaignEmailID = @emailid and ceenable = 0

select @cntfname = isnull(cFirstName,'') , @cntlname = isnull(cLastName,'') , @Emailaddress = isnull(cEmail,'') from tblContacts where cContactId = @ccntid and cnt_delete = 0

set @cntfullname = @cntfname + ' ' + @cntlname

set @emailbody1 = dbo.FNC_USERCRITERIA(@emailbody,@custid,cast(@Tcode as varchar(15)))

set @emailbody1 = dbo.FNC_CONTACTCRITERIA(@emailbody1,@ccntid)

set @emailbody1 = @emailbody1 + '<br><hr>If you feel that your receipt of this mailing has been in error... please <a href=http://www.domain.com/unsubscribe.aspx></font></a>'

set @emailsubject1 = replace(@emailsubject,'{%FirstName%}',@cntfname)

set @emailsubject1 = replace(@emailsubject1,'{%LastName%}',@cntlname)

set @emailsubject1 = replace(@emailsubject1,'{%myFname%}',@Userfname)

set @emailsubject1 = replace(@emailsubject1,'{%myLname%}',@Userlname)

EXEC @mail = sp_OASetProperty @MailID, 'From', @Useremail

-- EXEC @mail = sp_OASetProperty @MailID, 'MailFrom', 'deliveryfailures@domain.com'

EXEC @mail = sp_OASetProperty @MailID, 'FromName', @Userfullname

EXEC @mail = sp_OASetProperty @MailID, 'Body', @emailbody1

EXEC @mail = sp_OASetProperty @MailID, 'Subject', @emailSubject1

EXEC @mail = sp_OAMethod @MailID, 'AddAddress', NULL , @Emailaddress , @cntfullname

EXEC @mail = sp_OASetProperty @MailID, 'IsHTML', 1

EXEC @mail = sp_OAMethod @MailID, 'Send'

-- Here We will insert into undelivered

if @mail <> 0

After that we are having some code for send mails record etc.

--

I used user defined functions.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Can you do a repro ?

if yes, use Query Analyser to debug the proc and set brakepoints to these positions so you can check the content of all varriables and so you can see that all select statements return one and only on row.