Email Nightmare
-
I have been stuck with this for more than two weeks.
Can someone please help me with this?
I am writing an email program that will go to the projects table and determine if any of the projects is past the due date by at least one day. If that happens, the program will pull all the records that meet this condition and store them in developer_mail table.
This part of the stored proc called sp_auth_notify (see attached) works fine.
Then there is another sp called sp_send_cdontsmail (see attached) that is supposed to retrieve any records stored in developer_mail table and sends the mail to the designated reciprients.
This part, I think, works fine.
I say this because when I tested it by retrieving records from developer_mail and storing it in a temp table, it works great.
Now, I go to enterprise manager and set up jobs.
It executes the job and when you view job history, it tells you job successfully executed but no mail has ever been sent.
This is mystifying because no one seems to know what the real problem.
I use sp_send_cdontsmail because that does not require xp_sendmail, neither does it require sql mail.
Please look at my two SPs and tell me how to fix this if you know how because I have become a bottleneck to our project.
I set up jobs by typing exec sp_auth_notify in jobs.
sp_auth_nofity
**************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_AUTH_NOTIFY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_AUTH_NOTIFY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.SP_AUTH_NOTIFY
AS
BEGIN
DECLARE Notify_Cursor CURSOR FOR
SELECT lname, fname, manager, projName, email, status
FROM project
WHERE estCompDate < dateadd(dd, -1, getdate())
OPEN Notify_Cursor
Declare @lname varchar(50)
Declare @fname varchar(50)
Declare @assignedTo varchar(50)
Declare @projName varchar(20)
Declare @email varchar(1000)
Declare @status varchar(50)
-- Get the current MAX ID
Declare @mailID as int
-- Start reading each record from the cursor.
FETCH Notify_Cursor into @lname, @fname,@assignedTo, @projName,
@email, @status
WHILE @@FETCH_STATUS = 0
BEGIN
set @mailID = (SELECT max(mailID)+ 1 from developer_mail)
INSERT into developer_mail (mailid, lname, fname,
manager, projName, mailContent, sender, taskDate, status, email,
adminName)
VALUES (
@mailid,
@lname,
@fname,
@assignedTo,
@projName,
'This is a computer generated email message.
Please do NOT use the REPLY button above to respond to this email.
Dear ' + @assignedTo +'
Please be advised that project '+ @projName +' is past due by 1 day
or more.
If you wish to view the details of the project, please click here:
http://pw2/extendedSite/viewprojectList.asp?project_ID='+@projName+'
<http://pw2/extendedSite/viewprojectList.asp?project_ID='+@projName+'
Regards,
The Project Task Tracking System',
'PTTS Authorization Notification '+ @projName,
getdate(),
'unsent',
@email,
'IS Administrator'
)
FETCH Notify_Cursor into @lname,@fname,@assignedTo, @projName,
@email, @status
END
CLOSE Notify_Cursor
DEALLOCATE Notify_Cursor
END
BEGIN
DECLARE MAIL_CURSOR CURSOR FOR
select mailid, adminName, mailcontent, projname, email
from developer_mail
where status = 'unsent'
Declare @mail1 int
Declare @admin1 varchar(50)
declare @content1 varchar(5000)
Declare @proj1 varchar(20)
Declare @email1 varchar(1000)
OPEN MAIL_CURSOR
FETCH MAIL_CURSOR into @mail1, @admin1, @content1, @proj1, @email1
WHILE @@FETCH_STATUS = 0
BEGIN
--select @admin1, @content1, null, null, @proj1, @email1 -- you can
--uncomment this to test what is in each variable
exec sp_send_cdontsmail @admin1, @content1, null, null, @proj1,
@email1
FETCH MAIL_CURSOR INTO @mail1, @admin1, @content1, @proj1, @email1
END
CLOSE MAIL_CURSOR
DEALLOCATE MAIL_CURSOR
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sp_send_cdontsmail
*****************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_send_cdontsmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_send_cdontsmail]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@cc varchar(100) = 'sam.okeh@co.fulton.ga.us',
@BCC varchar(100) = 'sam.okeh@co.fulton.ga.us'
AS
Declare @MailID int
Declare @hr int
select @From,@To , @Subject , @Body, @cc , @BCC
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
thank you all in advance
-
Have a look at the following discussion and see if it gives you some ideas.
Also check this article to see if you've got things setup correctly,
http://support.microsoft.com/view/tn.asp?kb=312839
Thanks
Phill Carter
Edited by - phillcart on 02/17/2003 11:05:27 PM
--------------------
Colt 45 - the original point and click interface
-
hi phillcart!
I actually wrote this program by looking at your second link.
I think I have figured out why it is not working.
I just don't know how to fix it.
Take a look at this error message:
Unable to deliver this message because the follow error was encountered: "Error is processing file in pickup directory.".
The specific error code was 0xC00402CE.
I have tried just about everything I can think of but to no avail.
-
Sounds like you've got a bad msg file in the pick up folder. Try looking in the folder and checking each messge (or the bad folder).
Andy
-
Andy, what is a pick up folder.
I am more of a web developer than a sql guy.
Don't let the codes I wrote full you. It took me almost 3 weeks plus the sp_send_cdontsmail was copied from microsoft network.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply