Problem in sending Email through SQL server

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 in sending Email through SQL server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-03 : 07:48:33

Feudal writes "Hi,
I am using SMTP mail to send emails using SQL Stored Procedure.
When I execute the SP, I get the message "The command(s) completed successfully." but I do not receive any email.
This is real urgent. Could you please help me with this issue.
This is the SP that I used for this purpose.

CREATE Procedure sp_SMTPMail

@SenderAddress varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100)

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode <> 0
begin
print @resultcode
print 'method 0 failed'
end
if @resultcode = 0
BEGIN

EXEC @resultcode = sp_OAMethod @oMail, 'Send',NULL,@SenderAddress,@RecipientAddress,@Subject,
@Body
if @resultcode <> 0
begin
print @resultcode
print 'method 2 failed'
end
EXEC sp_OADestroy @oMail
END

SET nocount off
GO"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-03 : 08:45:49

I may be not 100% accurate with this
CDONTS is tough to get working because there is a default SMPT configuration that is unavailable to you.
CDOSYS the library that WIN2000 uses has the ability to specify these things. (server, port number)

If you have access or technical influence on the SQL Server install I can recommend registering the extended stored procedure available at www.sqldev.net which handles SMTP mail.

Also tons of EMAIL sprocs and situations by searching SQLteam forums and home page.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-03 : 15:57:33

I hate to state the obvious, But since so many of us (myself included) overlook the obvious.

You need to have smtp turned on at the server.

Also did You try Running this through QA ?

Jim
Users <> Logic

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-03 : 19:00:52

I agree with Sitka, get another mail component, like JMail or AspMail.

Also, have you checked the SMTP server log files ? What do they say ?

Damian

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-04 : 08:16:18

Merkin

Didint you once give me a work around for Master..xp_sendmail?

I can't seem to find it on a search.

Jim
Users <> Logic

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-04 : 16:48:19

I don't think it was me.
What did it work around ?

Damian