corigin.com

Hi everybody,

   I need to send emails periodically using sql server 2000.
   I used to methods but both are failed

1. When I try to use xp_sendmail then it throws the following error
"xp_sendmail: failed with mail error 0×80040106"

2. When I browse net, I found one SP which uses CDO objects, when try to execute that SP, suprisingly it doesn’t throw any error, but it failed to send email.

Here I am pasting that SP. I request you to plz guide me correct way.

Note: I am using Gmail’s smtp.gmail.com as smtp server name.

Thanks in advance.

Regards,

Rao

Create PROCEDURE [dbo].sp_SMTPemail
(
@From as nvarchar(50),
@To as nvarchar(50),
@Subject as nvarchar(255),
@Body as text
)
AS

– Declare
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 @config, ‘Fields(cdoSendUsingMethod)’, ‘cdoSendUsingPort’ — Send the message using the network
EXEC @hr = sp_OASetProperty @config, ‘Fields(cdoSMTPServer)’, ’smtp.gmail.com’ — SMTP Server
EXEC @hr = sp_OASetProperty @config, ‘Fields(cdoSMTPServerPort)’, 25 — Server SMTP Port
EXEC @hr = sp_OASetProperty @config, ‘Fields(cdoSMTPAuthenticate)’, ‘cdoAnonymous’ — Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, ‘Fields.Update’

– Message Object
EXEC @hr = sp_OASetProperty @message, ‘Configuration’, @config — set message.configuration = config
EXEC @hr = sp_OASetProperty @message, ‘To’, @To
EXEC @hr = sp_OASetProperty @message, ‘From’, @From
EXEC @hr = sp_OASetProperty @message, ‘Subject’, @Subject
EXEC @hr = sp_OASetProperty @message, ‘HTMLBody’, @Body
EXEC sp_OAMethod @message, ‘Send()’

– Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config

– Errorhandler
SELECT @hr

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

GO

 
…more