Database email isn't sending email anymore.

Question

Post reply

Database email isn't sending email anymore.

simflex-897410

Hall of Fame

Points: 3881
More actions
October 29, 2011 at 7:20 pm

#144496

Greetings Gurus,
I have a stored proc called sp_Reminder.
It has 4 parameters:
@Fullname, @Employee_Id, @Email, @Dept_Code
Here is the relevant part
FETCH Reminder_Cursor into @FullName,@employee_id,@email,@dept_code
END
CLOSE Reminder_Cursor
DEALLOCATE Reminder_Cursor
END
BEGIN
DECLARE MAIL_CURSOR CURSOR FOR
select mailid, sender, mailcontent
from PledgeNotification
where Sent = 'No'
Declare @mail1 int
Declare @sender nvarchar(100)
declare @content1 nvarchar(4000)
OPEN MAIL_CURSOR
FETCH MAIL_CURSOR into @mail1, @sender,@content1
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_send_cddontsmail @mail1, null,null,@content1,null,nullAs you can see, it calls a stored proc called sp_send_cdontsmails.
I have used this stored proc since 2001 and it has always worked until we upgraded to sql server 2005.
Now, it is no longer sending email. No errors but no emails are being sent.
After some research, I started to believe that sp_send_cdontsmail is now deprecated.
So far, I have been trying to switch to sp_send_cdosysmail.
So instead of
exec sp_send_cddontsmail @mail1, null,null,@content1,null,null
I now use this:
exec sp_send_cdosysmail @mail1, null,null,@content1,null,null
I get an error that I am using an invalid address.
Below are the the stored procs, the old sp_send_cdontsmail and the new sp_send_cdosysmail
Can someone please help?
Many thanks in advance.
--******OLD sp_send_Cdontsmail STORED PROC**********
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From nvarchar(100),
@To nvarchar(100),
@Subject nvarchar(100),
@Body nvarchar(4000),
@cc nvarchar(100),
@BCC nvarchar(100)
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From','me@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user1@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user2@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'Subject', 'Thank you for Presentation'
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
--******LATEST sp_send_CDOSYS STORED PROC**********
CREATE PROCEDURE [dbo].[sp_Notify]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)="Thank you for the Presentation",
@Body varchar(4000),
@cc nvarchar(100),
@BCC nvarchar(100)
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @MailID int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @MailID OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @MailID, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @MailID, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtprelay.mycompany.net'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @MailID, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OASetProperty @MailID, 'From', 'me@domain.com'
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @MailID, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user1@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'BCC', 'user2@domain.com'
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @MailID
PRINT 'Mail Sent!'

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

~~Change all of the double quotes to single quotes to start with...~~

Scratch that... I was thinking of something else... :blush: the double quotes are required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Hi Jeff,

I read any articel written by you.

They are enriching and helpful.

About my issue, I have resolved it.

I am now using send_dbMail. As it turns out, I was not using the correct smtp server name.

Once, I resolved that it started working.

I do have a question though.

When I ported the same code to production server, with sql server 2008 running on it, it isn't working anymore.

I have enabled dbmail.

I have also enabled ole Automation.

Finally, I have set up a profile on sql server 2008 like I did on sql server 2005.

Do I need to install smtp server on that server for it to work?

I got this error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 6 (2011-10-30T00:36:35). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )

I tried a few times but got same error.

I was under the impression that smtp install is not necessary.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

First, my apologies. I said to remove the double quotes and I'm not sure what I was thinking because the double quotes you had are required.

Shifting to your latest post, you say you have it working but then you post an error. Is it working on the machine you want or not?

Also, no... you shouldn't have to install SMTP on SQL Server to get this to work. But, you do appear to be missing a couple of steps... (replace the xxx.xxx.xxx.xxx with either the correct name or IP address. You mah have to verify the port as well. I never had to change username or password from what it is as suspect that might be a bit of a security problem at my old company)

--===== Setup the configuration to create a CDOSYS.dll/SMTP email message.

-- Trust me... none of this is optional. If you want to know what all of this does,

-- you need to Google the HTTP addresses which aren't really addresses at all.

EXEC dbo.sp_OACreate 'cdo.message', @objEmailID OUT;