Can't send email via SQL Server 2

[ranchan02]

Hi Everyone:

I got this piece of code from the internet. What it suppose to do is send an email from a stored procedure. I created the stored procedure and it compiles fine. The funny thing is that when I try to execute it in the Query Analyzer:

EXEC sp_SMTPemail 'from@example.email.com', 'me@mydomain.com', 'Subjecttext','Messagetext'

it runs, but I don't get an email. I'm not sure why it doesn't do anything...could someone assist me on how to troubleshoot this?

CREATE PROCEDURE 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)', 'your.server.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, 'TextBody', @Body
EXEC 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, Description=@desc
RETURN
END
GO

 

[adamroof]

Do u have the proper values in the Configuration Object section?

EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com'

 

[ranchan02]

Hi adamroof:

Yeah I have the proper value in that line. Any other suggestion on how to trace it?

 

[adamroof]

I just applied everything as you had it, changed my smtp server to my smtp server and it went fine and recieved fine!

Does your smtp require authentication?
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous'

alot now do, if it is not your exchange server, or other.
Make sure your ISP is not blocking port 25, if you are not in corporate environment.

POP3 auth... try
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoBASIC'
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUserName)', 'myemailbox@myemail.com'
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendPassword)', 'mysupersecretpasswordforemailbox'

Exchange auth... try
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUserName)', 'mydomain\myuser'
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendPassword)', 'mysupersecretpasswordfordomain'
mydomain\myuser

 

[mrdenny]

The sp_OA procedures are very hard to troubleshoot. I'd recommend googling for xp_smtp_sendmail, downloading and installing it. It's much easier and works easily first time.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com