Fail to send mail using CDOSYS

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 2005 Forums

Transact-SQL (2005)

Fail to send mail using CDOSYS

Author

Topic

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-12-29 : 11:23:27

I am a newbies in SQL Server.
I am not able to send mails using CDO
Please Help

Following is the error i am getting
Source: CDO.Message.1
Description: The transport failed to connect to the server.

I am using same SMTP server name that i use in my outlook
in company LAN and SQL Server is also on the same machine on company LAN
IIS is not installed
got following error when used command telnet SMTPSERVERNAME 25
Connecting To SMTPSERVERNAME...Could not open connection to the host, on port 25: Connect failed

following is the code I am using
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100)=null ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
if @from = null
begin
select @from=@@servername
end
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','SMTP01'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-29 : 11:34:26

in 2005 use database mail instead of cdosys.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-12-30 : 05:51:58

I tried with database mail with same SMTP server name and got following error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-12-29T21:19:22). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

Please help

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-12-30 : 06:44:38

It looks like your SQL Server can't connect to your mail server

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-12-30 : 09:05:24

then any idea what to do
this is the same SMTP server that I use in Outlook
please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources