SQL Server Performance Forum – Threads Archive

sp_send_cdosysmail

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>