July 8, 2006 at 1:08 am
hi,
I have a problem sending mail. i have written a stored procedure which uses CDOSys.
My problem is : When i executed it in query analyzer, it gives message "Command Successfully Executed" but no mail is send to the recipient.
Moreover similar code i have written in ASP, its working properly. Mails are being sent......
I m pasting the code below:
CREATE procedure sp_CDOSendMail
AS
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
---EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object
-- Configuration Object
/*EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendusing).Value',2 -- Send the message using the network
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas. microsoft.com/cdo/configuration/smtpserver).Value', '172.16.2.53' -- SMTP Server
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/smtpserverport).Value',25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendusername).Value', username
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendpassword).Value', password --password
EXEC @hr =sp_OAMethod @message, 'Configuration.Fields.Update'
EXEC @hr = sp_OASetProperty @message, 'To','jagriti_23@yahoo.co.in'
EXEC @hr = sp_OASetProperty @message, 'cc','neetals@gmail.com'
EXEC @hr = sp_OASetProperty @message, 'From','datapoint@ncl.res.in'
EXEC @hr = sp_OASetProperty @message, 'Subject','newStored Proc Check'
EXEC @hr = sp_OASetProperty @message, 'HTMLBody','This is a stored proc generated mail'
EXEC @hr =sp_OAMethod @message, 'Send()'
-- Destroys the objects
EXEC @hr = sp_OADestroy @message
--EXEC @hr = sp_OADestroy @config
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, escription=@desc">Description=@desc
RETURN
END
GO
July 11, 2006 at 9:13 am
Two things: First, get off this. It isn't in W2K3 (although you can jump through hoops to get it there). Better to use the free one from ASPMail (http://www.aspemail.com/).
Second, is the SMTP service running on the SQL Server?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
