October 29, 2011 at 7:20 pm
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.
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!'
October 30, 2011 at 10:51 am
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
October 30, 2011 at 1:31 pm
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
October 30, 2011 at 4:03 pm
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;
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2';
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','xxx.xxx.xxx.xxx';
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25';
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1';
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','username';
EXEC dbo.sp_OASetProperty @objEmailID,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','password';
EXEC dbo.sp_OAMethod @objEmailID,
'Configuration.Fields.Update', NULL;
--Jeff Moden
October 30, 2011 at 5:35 pm
First, I am no longer using the sample you just posted.
I am using the msdb.send_dbMail component.
This now works perfectly on my test server which runs on Windows Server 2003 and SQL Server 2005.
However, I when deployed to production server which runs on Windows Server 2008 and SQL Server 2008, I keep getting the timeout error I posted earlier.
Any ideas what I could be doing wrong?
October 30, 2011 at 8:30 pm
I went through this at a previous company. Even the SQL Alerts couldn't send mail after they made the move and I left the company before I found out what they did wrong. I suspect they simply didn't setup SQL Server email correctly or maybe at all.
I was able to get around that by using the IP address of the real email server as I did in my example, though.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply