Problem sending email using stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem sending email using stored procedures

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2005-01-10 : 21:47:37

Hi all,
I have this stored procedures called Send_Email using CDO to send email out , in my code i am using the IP address of my email server to send outgoing email but it seem that the settings set in the stored procedures are unable to take my settings , it is always using my local SMTP server to send out email instead of the company smtp server IP. Although it is successfully sent a email and i received it from my developing server , if can let i am going to deploy on the real-time server itself , the email does not successfully send out thought is not not throwing any error.

What can i do is to send out email from the company smtp server

this is my Stored Procedures to send out email

CREATE PROCEDURE [dbo].[Send_Email]

@RecipientAddress varchar(500),
@RecipientName varchar(800),
@SenderAddress varchar(80),
@SenderName varchar(150),
@Subject varchar(200),
@Body varchar(500)

AS

Declare @oMail int
Declare @oMessage int
Declare @iSuccess int
Declare @oConfig int
Declare @src as varchar(500)
Declare @desc as varchar(500)

--SMTP Server Settings--
Declare @mailServer as varchar(80)
Set @mailServer = '166.77.69.25'

EXEC @iSuccess = sp_OACreate 'CDO.Message', @oMessage OUT -- create the message object
EXEC @iSuccess = sp_OACreate 'CDO.Configuration', @oConfig OUT -- create the configuration object

BEGIN

if @iSuccess= 0

---Configuration Object
EXEC @iSuccess = sp_OASetProperty @oConfig, 'Configuration.Fields(cdoSMTPServer).Value', '166.77.69.25' -- SMTP Server
EXEC @iSuccess = sp_OASetProperty @oConfig, 'Configuration.Fields(cdoSendingUsingMethod).Value', 2 -- Server Sending Port
EXEC @iSuccess = sp_OASetProperty @oConfig, 'Configuration.Fields(cdoSMTPServerPort).Value', 25 -- Server SMTP Port
EXEC @iSuccess = sp_OASetProperty @oConfig, 'Configuration.Fields(cdoSMTPAuthenticate).Value', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @oConfig, 'Configuration.Fields.Update'

EXEC @iSuccess = sp_OASetProperty @oMessage, 'To', @RecipientAddress
EXEC @iSuccess = sp_OASetProperty @oMessage, 'From', @SenderAddress
EXEC @iSuccess = sp_OASetProperty @oMessage, 'Subject', @Subject
EXEC @iSuccess = sp_OASetProperty @oMessage, 'TextBody', @Body
EXEC sp_OAMethod @oMessage, 'Send()'

--realise object
EXEC @iSuccess = sp_OADestroy @oMessage
EXEC @iSuccess = sp_OADestroy @oConfig

-- Errorhandler
IF @iSuccess <> 0
BEGIN
EXEC sp_OAGetErrorInfo @oMessage, @src OUT, @desc OUT
SELECT iSuccess=convert(varbinary(4),@iSuccess), Source=@src, Description=@desc
RETURN
END

END
GO

kt

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-11 : 09:23:07

1.)
are you sure this is correct...

cdoSendingUsingMethod

I thought it was cdoSendUsingMethod or cdoSendUsing

2.) Permissions
-try changing from development SQL server and it's default SMTP - to -development SQL server and live email server.
that is don't introduce two points of failure at once, a differnet SQL server AND a different email server (divide it in half)
-sp_OACreate

I wish someone would start an Official XML Rant Thread.

verybrightstar
Starting Member

16 Posts

Posted - 2005-01-11 : 11:40:09

sorry i don't get what you are trying to say for the second point. Could you please further elaborate ? thanks

quote:
Originally posted by Sitka

1.)
are you sure this is correct...

cdoSendingUsingMethod

I thought it was cdoSendUsingMethod or cdoSendUsing

2.) Permissions
-try changing from development SQL server and it's default SMTP - to -development SQL server and live email server.
that is don't introduce two points of failure at once, a differnet SQL server AND a different email server (divide it in half)
-sp_OACreate

I wish someone would start an Official XML Rant Thread.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-11 : 12:49:19

I read your description as

You developed this procedure and it runs in a development environment
meaning a local email server and local SQL install.

I merely suggested change one thing at a time.

Run the procedure on the development SQL server but point it to
the live email server.
As opposed to....
Run the procedure on the live server AND change which email server.

It is just a debug approach, don't change two things at once when it works "here" but not over "there".

You may have tried that already, just be sure.

What about the other point #1.

cdoSendingUsingMethod does not even exist on Google, it would be a rare case if it was an actual property of the CDO.Configuration object, curious.

I wish someone would start an Official XML Rant Thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources