sp_send_cdosysmail | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


Recently I upgraded a couple of my SQL Servers to run on Windows Server 2003. They are both SQL Server 2000 SP3a Enterprise Edition that have been taken to SQL Server code build 997 (at Microsoft’s suggestion). Since making these changes I can no longer get the stored procedure sp_send_cdosysmail to send email. Scripts will complete successfully without any errors but the email message nevers comes through. Has anybody else experienced this problem? Everything was fine until I upgraded the OS. Is there something special about Windows Server 2003 that I have to configure or change to make this stored procedure send the email?
See if this helps you CREATE PROCEDURE SendMail(
@From varchar(255),
@To varchar(255),
@Message varchar(8000),
@Subject varchar(255))
AS DECLARE @CDO int, @OLEResult int, @Out int –Create CDONTS.NewMail object
EXECUTE @OLEResult = sp_OACreate ‘CDONTS.NewMail’, @CDO OUT
IF @OLEResult <> 0 PRINT ‘CDONTS.NewMail’
EXECUTE @OLEResult = sp_OASetProperty @CDO, ‘BodyFormat’, 0
EXECUTE @OLEResult = sp_OASetProperty @CDO, ‘MailFormat’, 0 –Call Send method of the object
execute @OLEResult = sp_OAMethod @CDO, ‘Send’, Null, @From, @To, @Subject, @Message, 1 –0 is low 1 is normal
IF @OLEResult <> 0 PRINT ‘Send’ –Destroy CDO
EXECUTE @OLEResult = sp_OADestroy @CDO return @OLEResult Madhivanan Failing to plan is Planning to fail
check whether this help you http://www.sql-server-performance.com/dh_sending_sql_notifications_CDOSYS.asp
Have you restarted SQLAgent service when you’ve setup the mail profile, sometimes this will have good results.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.