Article: Sending SMTP Mail using a Stored Procedure

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

Site Related Forums

Article Discussion

Article: Sending SMTP Mail using a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-22 : 15:02:16

One of the downsides of SQL Mail is the fact that it is tied to a MAPI Profile. Here is a way to send email using the sp_OACreate procedure to call a third party SMTP component.

Article Link.

wlk78
Starting Member

1 Post

Posted - 2001-12-07 : 04:29:12

I am trying to user the extended storedprocedur writen by one of the member, the source can be get at the url below :

http://www.babaluga.com/dl/xpsendmail.zip

to execute the stored procedure, i have follow the instruction

EXEC xp_sendSMTP 'SMTP.server', 'from', 'to', 'subject', 'body'

can i know what is smtp server what should i put in ???

now i am using windows 2000 server, it comes together with smtp service, is smtp services same with smtp server, or i need to install another smtp server ?????

can anyone reply me as soon as possible .....

rudib
Starting Member

2 Posts

Posted - 2002-01-03 : 04:12:10

You can use the SMTP service. A SMTP server is just a machine running a deamon dedicated to exchange mail. Most of the "public" SMTP servers will not allow you to send mail through them (that is called relaying). If you've an ISP, probably you can use its SMTP server

quote:

EXEC xp_sendSMTP 'SMTP.server', 'from', 'to', 'subject', 'body'

can i know what is smtp server what should i put in ???

now i am using windows 2000 server, it comes together with smtp service, is smtp services same with smtp server, or i need to install another smtp server ?????

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2002-01-03 : 11:28:06

For the ones that were able to get the extended stored procedure to work, can you provide the type of connection that you use for SQL Server and for the SMTP agent?

I am able to run the procedure and I get a error that states 'Connection to SMTP refused' and I am wondering if it has something to do with the way that the SMTP is configured.

Thanks,
Scooter McFly

Rafiq
Starting Member

25 Posts

Posted - 2002-01-05 : 07:34:26

quote:

FYI - Here's the modified script to get this to work with CDONTS (which is free).


Create Procedure sp_SMTPMail
	@SenderName varchar(100),
	@SenderAddress varchar(100),
	@RecipientName varchar(100),
	@RecipientAddress varchar(100),
	@Subject varchar(200),
	@Body varchar(8000)
AS	
	SET nocount on
	
	declare @oMail int --Object reference
	declare @resultcode int
	
	EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
	
	if @resultcode = 0
	BEGIN
		EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
		EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
		EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
		EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
	
		EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
	
		EXEC sp_OADestroy @oMail
	END
	

	SET nocount off
GO

To test, call with something like:


exec sp_SMTPMail @SenderName='You', @SenderAddress='YourName@YourMailSite.com',
@RecipientName = 'YouAgain', @RecipientAddress = 'YourName@YourMailSite.com',
@Subject='SQL Mail Test', 
@body='This is a test message from SQL Server.  Smile! It worked.'

The only catch is getting CDONTS installed on your SQL Server without dumping excess junk there. I found one of our SQL Servers had IIS installed on it by mistake, and CDONTS came with it.

Should this (and Merkin's Original) go into the Script Library Forum?

Hi mark,

How to get this .dll file. Tell me.

Regards,

Rafiq
------------------------------------
If you think, you can do anything...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-05 : 21:39:36

Rafiq, not to be rude, but it appears that you did not read the full thread before posting. Several posts above yours on page 3 of the discussion I included a link to Microsoft's web site about Where to get the CDO libraries. I'm sure that will help you out.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

simon
Starting Member

2 Posts

Posted - 2002-01-16 : 05:55:11

Hi,

I ran this stored procedure for sending mail from within query analyser and nothing happened. I didn't get an error message for the syntax when i created the procedureor when i called it just no email got sent. I also tried the cdonts version in the forum list, again nothing.

Is there a way of testing where this went, no files/mails were dropped in my mail root or badmail folders.

Thanks

Simon

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-18 : 12:53:17

Simon,

Because of the way that @@Error only relates to the most recent command, you might want to drop in tests for the value of @@Error at each step to locate which one is failing.

Also, have you sent CDONTS mail from this server before? Do you know if the SMTP Service is configured properly? You might create a VB program or a VBScript scriptlet to test it out outside of SQL.

--------------------------------
There's a new General in town...

simon
Starting Member

2 Posts

Posted - 2002-01-24 : 08:39:45

Yes I have been using cdonts.dll to send email messages using asp (vb script) for months. There is no problem with this

topry
Starting Member

1 Post

Posted - 2002-03-07 : 15:44:16

Thanks to the original author for bringing up this topic. I too was unhappy with SQLMail's limitations, but had not considered this option. Since we use JMail vs ASPMail, I thought I would pass this along to others that do as well:
Here is a very small SProc for sending simple text emails-

CREATE Procedure usp_SMTPMail
/*
Uses freeware COM object JMail.dll to send simple email
*/
@SenderAddress varchar(100), --the senders email address on a restricted system it must be a valid email address REQUIRED
@RecipientAddress varchar(100), --recipients address(es). If more than one, separate with a comma REQUIRED
@Subject varchar(200), --optional subject line
@Body varchar(8000), --text body
@MailServer varchar(100) --SMTP server through which this will be sent REQUIRED

AS

SET nocount on

declare @oMail int --JMail Object
declare @resultcode int

--create the JMail object
EXEC @resultcode = sp_OACreate 'JMail.Speedmailer', @oMail OUT

--If it succeeded, then send the mail
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL, @SenderAddress, @RecipientAddress, @Subject, @Body, @MailServer
EXEC sp_OADestroy @oMail
END

SET nocount off
GO

For an SProc using JMail that can include attachments, CC, BCC: http://www.activeservers.com/components/Jmail/jmail_and_sql.htm
For the latest version of JMail: http://www.dimac.net/

danmorph
Starting Member

1 Post

Posted - 2002-04-01 : 18:11:45

further to the 255 char restriction, when calling cdonts from a stored proc: a way to get around this is by using the text datatype for @body instead of varchar.

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-04-18 : 10:25:37

quote:

Has no-one tried to send a mail longer than char(250), or is everyone just hitting the same brick wall that has stopped me, I am trying a workaround and if it works I will post it, has noone else come up against and or solved this string truncation problem.

There must be a way round it, I have a theory which I am currently testing, is there anyone out there with superior knowledge of visual basic data types?

amolgokhale
Starting Member

3 Posts

Posted - 2002-04-19 : 07:36:25

Hi,

I was able to get the script working. But I want to send mails in html. So I want ot invoke the bodyformat property, but it seems I am not able to do that somehow. Can you guide me on that?

Edited by - AjarnMark on 07/26/2001 19:43:49
[/quote]

amolgokhale
Starting Member

3 Posts

Posted - 2002-05-04 : 07:43:22

Hi,

I may be making a mistake here, but I think a lot of people may not have read my last post. I am able to use the CDONTS component to send mail thru my stored procedure. I now want to add html content to the mail so that the mail becomes better looking & has more graphics & stuff. But I am unable to do that. I tried a lot of things with mail format & bodyformat properties of CDONTS. But none of them seems to work. I also searched high & low on the net, but problem remains unsolved.

Anybody has any idea what I am doing wrong. I have pasted my code below.

Bye,
Amol Gokhale

CREATE Procedure sp_SMTPmail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress1 varchar(100),
@RecipientAddress2 varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = '200.200.200.240'
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.Newmail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'Remotehost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress1
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress2
/*EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName*/
/*EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress*/
/*EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress1
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress2*/
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Mail', NULL
EXEC sp_OADestroy @oMail
END
SET nocount off

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-05-04 : 10:30:36

Try this: http://support.microsoft.com/view/tn.asp?kb=312839

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

amolgokhale
Starting Member

3 Posts

Posted - 2002-05-05 : 03:47:12

quote:

Try this: http://support.microsoft.com/view/tn.asp?kb=312839

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Already saw that but unfortunately its very cryptic. It has got everything but a straight forward answer to my problem. Well I guess I'll keep on trying. If anything turns up do post it though

cycomyco
Starting Member

1 Post

Posted - 2002-05-06 : 12:03:58

quote:

DOH!

OK Sorry guys. You got me there, I didn't consider that one

I have another way of doing it which I am writing into an article now.

Give me a few days

Sorry i'm using the cdonts documented in the MSDN Library for sending messages from SQL Server and i find the problem with messagess up to 255 characteres.
can you help me with this.

Thanks

Damian

Mammouth
Starting Member

1 Post

Posted - 2002-05-30 : 16:01:20

Why my varchar(8000) are stripped to 6000 when i pass it to the sp_OAMethod?

KHeon
Posting Yak Master

135 Posts

Posted - 2002-06-05 : 13:55:14

I modifed the proc to use CDONTS on a local dev server and also changed the VARCHAR(8000) to TEXT but for some reason I don't get the body when I execute the proc with a string longer then about 3800-4000 (haven't determined where it exactly breaks). This however works fine when done through ASP.

WIN2K Server / IIS5
SQL Server 2000

Any thoughts?

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-07-29 : 16:24:50

I am missing one logical step here.
On the development server SQL/IIS all these sp_OACreate methods work fine but the production server dosen't have IIS or the default Virtual SMTP Server.
W3 Jmail says....
The MS SMTP service must be installed on the machine where w3 JMAIL is to be used (or on a remote machine where w3 Jmail can access its pickup directory)

JMail.Message.MsPickupDirectory = C:\INETPUB\MAILROOT\PICKUP

Is that it? sounds great. The SQL Server could have a mapped drive to IIS. Is this how JMAIL works?

And just before I go. Does CDONTS have the equivalent property for newMail?

Voted best SQL forum nickname...."Tutorial-D"

Edited by - Sitka on 07/29/2002 16:28:04

telania
Starting Member

1 Post

Posted - 2002-07-31 : 16:32:08

Sorry guys but I pretty new to this SQL thing.

Ok my woes started like this: I upgraded my hosting account and opted for SQL 2000 package. Great... Problem is I had previously used CDONTS on my ASP pages and the worked fine.
SO now I have gotten my forms to dump data into my sql server. My problem now is getting CDONTS to work. I opted for a couple of new scripts but I keep getting some errors.
====================================================================
Bear with me while I post my code
==================================================================
<%

' Create the connection to the database
set conn = server.createobject ("ADODB.Connection")

' Create the SQL statement to query the database
SQL="SELECT * FROM PPM"

mySQL= "INSERT INTO PPM"
mySQL= mySQL & "(order_manual,FirstName,LastName,Title,Company,Address,Address2,City,State,ZipCode,Country, Phone,OState, Fax,Email,NumEmployees, HRTip, Opt, Filename,HTTP_REFERRER,REMOTE_ADDR) "
mySQL= mySQL & "VALUES ('" & Request.Form("Order_manual") & "','"
mySQL= mySQL & Request.Form("FirstName") & "','"
mySQL= mySQL & Request.Form("LastName") & "','"
mySQL= mySQL & Request.Form("Title") & "','"
mySQL= mySQL & Request.Form("Company") & "','"
mySQL= mySQL & Request.Form("Address") & "','"
mySQL= mySQL & Request.Form("Address2") & "','"
mySQL= mySQL & Request.Form("City") & "','"
mySQL= mySQL & Request.Form("State") & "','"
mySQL= mySQL & Request.Form("ZipCode") & "','"
mySQL= mySQL & Request.Form("Country") & "','"
mySQL= mySQL & Request.Form("Phone") & "','"
mySQL= mySQL & Request.Form("OState") & "','"
mySQL= mySQL & Request.Form("Fax") & "','"
mySQL= mySQL & Request.Form("Email") & "','"
mySQL= mySQL & Request.Form("NumEmployees") & "','"
mySQL= mySQL & Request.Form("HRTip") & "','"
mySQL= mySQL & Request.Form("Opt") & "','"
mySQL= mySQL & Request.Form("Filename") & "','"
mySQL= mySQL & Request.ServerVariables("HTTP_REFERRER") & "','"
mySQL= mySQL & Request.ServerVariables("REMOTE_ADDR") & "')"

'Execute the SQL statement and store the results in the rs object
set rs = conn.execute(mySQL)

'
'--=============================================================================================
'--== Name - sp_SMTPMail
'--== Purpose - Send an email using an SMTP Mailer COM Component
'--== Input Parameters - @SenderName, @SenderAddress, @RecipientName, @RecipientAddress
'--== @Subject, @Body, @MailServer
'--==
'--==
'--== Usage - exec sp_SMTPMail @SenderName='Damian', @SenderAddress='damian@bdussy.com',
'--== @RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com',
'--== @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
'--==
'--== Created Date - Thursday, July 19, 2001
'--== Created by - Damian Maclennan
'--== merkin@sqlteam.com
'--== www.bdussy.com/damian
'--==
'--== Disclaimer - This procedure has been created for demonstration purposes. It has not
'--== been created for production purposes and I hold no liability for any
'--== unwanted effects of running it.
'--=============================================================================================
'/

Create Procedure sp_SMTPMail

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'

AS

SET nocount on

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

EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', ppspublisers.com
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', Donatus
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', donatus@**.com

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, Me, donatus@**.com

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off
GO

%>

===================================================================
ANy help is appreciated

Don

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources