March 21, 2005 at 7:11 am
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. We are using sql server agent job to exceute the store procedure.
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
March 24, 2005 at 8:00 am
This was removed by the editor as SPAM
March 28, 2005 at 9:23 pm
Without seeing your code, it is difficult to guess. One thought is that you may be trying to add NULL values when building your string. For example, say that I have a LastName value but the FirstName is null. When I concatenate the string like this: FirstName + ' ' + LastName, I will end up with a null.
Since this is a stored procedure, I would add some print statements to it for debugging purposes. Comment out the actual statement that sends the mail to avoid annoying everyone on the list. In a dev environment, leave only problem rows in your contact list table. User the QA Debugger to step through the code and watch the variables as they change.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 28, 2005 at 9:42 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply