How to send Email using Sql Server 2000

Question

Post reply

How to send Email using Sql Server 2000

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

Check this

http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

http://www.sql-server-performance.com/articles/dba/email_functionality_p1.aspx

http://www.sqlservercentral.com/articles/Administration/introtodatabasemailinsql2005/2197/

http://www.databasejournal.com/features/mssql/article.php/3626056

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 2

Hi neelamkadam82

I know it sounds obvious but please ensure you have copied the required DLL file in the correct directory as per your configurations.

Follow the Installation Module on the same page & I don't see much reasons for an issue to crop up.

I've been able to successfully integrate & use the same in my servers also.

If you still stuck up revert back to the forum with the exact error message & what all steps you've already performed.

Ankit Mathur

Neil-633985 SSC Enthusiast Points: 101 More actions · Answer 3

Hi Ankit

I had saved the XPSMTP.DLL in the folder

C:\Program Files\Microsoft SQL Server\MSSQL\BINN\

and then I had excuted the above lines.

My requirement is sending attachments in email through SQL Server. But I dont want to depend on the Outlook.

Neil-633985 SSC Enthusiast Points: 101 More actions · Answer 4

Thanx Ramon

I had already tried executing the code given in the URL. But it is also not working 🙁

Ramon Jimenez Default port Points: 1467 More actions · Answer 5

Hi Neil,

Are you getting any error messages?

Do the smtp server allow the relay?

You can verify it by trying to open a telnet session to smtp port.

Regards Ramon

Neil-633985 SSC Enthusiast Points: 101 More actions · Answer 6

Hi Ramon

I had executed the steps as per instructed in the procedure.

While executing the code:

declare @Body varchar(4000)

select @Body = 'This is a Test Message'

exec sp_send_cdosysmail 'someone@example.com','someone2@example.com','Test of CDOSYS',@Body..

I am getting the eror as

Source: CDO.Message.1

Description: The transport failed to connect to the server.

On debugging, I found that the error came at the point of execution of the below code:

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

How to verify the SMTP thru TELNET?

Ramon Jimenez Default port Points: 1467 More actions · Answer 7

Neil,

Verify this checklist:

you have created sp_send_cdosysmail procedure

the user which you are connected can execute the procedure

you have entered modify sp_send_cdosysmail with your smtp server parameters.

the mail user you are trying to use is accepted by mail server.

if you want to verify telnet connection to a SMTP server check this link from MS

How to Use Telnet to Test SMTP Communication

Regards Ramon

vijay.chendur Valued Member Points: 66 More actions · Answer 8

try this simple and works find microsoft example:

CREATE PROCEDURE [dbo].[sp_TestMail]

@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

--------------------------------------------

sp_testMail'vijay.chendur@allsectech.com','vijay.chendur@allsectech.com','Subject','Body','CCC','BCC'

vijay