why cannot I send e-mail using SQL Server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

why cannot I send e-mail using SQL Server?

Author

Topic

dogli
Starting Member

32 Posts

Posted - 2002-09-05 : 13:11:24

Hi guys,

I am trying to send e-mail using SQL Server and CDONTS. I test it with the source code from Microsoft Q312839 as below:

1) I create a stored procedure:

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

2) Then I execute it in SQL Query Anlyzer with the code belows:
exec sp_send_cdontsmail 'someone@company.com','someone2@company.com','Test of CDONTS','It works'

After I execute the command, I get the message as below:
"The command(s) completed successfully."
-----------------------------------------------------------------

But I just cannot receive any message in e-mail box. Because I can send e-mail with CDONTS in the same server within ASP code, so I think my SMTP server is running.

Do you think what's the possible problem?

I appreciate any help you give me.

dogli

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-05 : 15:39:33

Have a look in Inetpub\mailroot\Badmail, is there anything in there ?
Also , you should call sp_OAGetErrorInfo after each call as this will give you better error messages if anything goes wrong e.g.


Declare @MailID int 
Declare @hr int 
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OASetProperty @MailID, 'To', @To 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID 
EXEC @hr = sp_OADestroy @MailID 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @MailID

HTH
Jasper Smith

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-06 : 09:26:28

Is this error info returned so an ASP routine can process it?

SamC

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-06 : 17:29:21

Not sure if this will help you at all, but if you're running Win2k you can use CDOSYS instead of CDONTS.

Worked like a charm for me, just did it a few days back... Did not try the CDONTS version, sorry...

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839

Edited by - Jeepaholic on 09/06/2002 17:30:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources