SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Sending SMTP Mail using a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

scootermcfly
Yak Posting Veteran

USA
66 Posts

Posted - 01/03/2002 :  11:28:06  Show Profile  Click to see scootermcfly's MSN Messenger address  Reply with Quote
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


Go to Top of Page

Rafiq
Starting Member

India
25 Posts

Posted - 01/05/2002 :  07:34:26  Show Profile  Visit Rafiq's Homepage  Reply with Quote
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...
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/05/2002 :  21:39:36  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

simon
Starting Member

2 Posts

Posted - 01/16/2002 :  05:55:11  Show Profile  Reply with Quote
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

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/18/2002 :  12:53:17  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

simon
Starting Member

2 Posts

Posted - 01/24/2002 :  08:39:45  Show Profile  Reply with Quote
Yes I have been using cdonts.dll to send email messages using asp (vb script) for months. There is no problem with this

Go to Top of Page

topry
Starting Member

1 Posts

Posted - 03/07/2002 :  15:44:16  Show Profile  Reply with Quote
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/

Go to Top of Page

danmorph
Starting Member

1 Posts

Posted - 04/01/2002 :  18:11:45  Show Profile  Reply with Quote
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.

Go to Top of Page

Kevin Snow
Posting Yak Master

149 Posts

Posted - 04/18/2002 :  10:25:37  Show Profile  Reply with Quote
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?






Go to Top of Page

amolgokhale
Starting Member

3 Posts

Posted - 04/19/2002 :  07:36:25  Show Profile  Reply with Quote
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]

Go to Top of Page

amolgokhale
Starting Member

3 Posts

Posted - 05/04/2002 :  07:43:22  Show Profile  Reply with Quote
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


Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/04/2002 :  10:30:36  Show Profile  Visit graz's Homepage  Reply with Quote
Try this: http://support.microsoft.com/view/tn.asp?kb=312839

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

amolgokhale
Starting Member

3 Posts

Posted - 05/05/2002 :  03:47:12  Show Profile  Reply with Quote
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



Go to Top of Page

cycomyco
Starting Member

1 Posts

Posted - 05/06/2002 :  12:03:58  Show Profile  Reply with Quote
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



Go to Top of Page

Mammouth
Starting Member

1 Posts

Posted - 05/30/2002 :  16:01:20  Show Profile  Visit Mammouth's Homepage  Reply with Quote
Why my varchar(8000) are stripped to 6000 when i pass it to the sp_OAMethod?

Go to Top of Page

KHeon
Posting Yak Master

USA
135 Posts

Posted - 06/05/2002 :  13:55:14  Show Profile  Visit KHeon's Homepage  Reply with Quote
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
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 07/29/2002 :  16:24:50  Show Profile  Reply with Quote
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
Go to Top of Page

telania
Starting Member

1 Posts

Posted - 07/31/2002 :  16:32:08  Show Profile  Reply with Quote
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

Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 08/02/2002 :  13:41:12  Show Profile  Reply with Quote
First try at helping someone.
'watch out...big drunk guy...comin' thru'

I upgraded my hosting account and opted for SQL 2000 package. Great.
Could mean the use of cdosys on win2k iis but whatever.

It normally goes something more like this

' Your don't need
'SQL="SELECT * FROM PPM"

set conn = Server.CreatObject("ADODB.Connection")
conn.Open <your connection string>

'If someones orders your book you build the string like you have
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") & "')"

'and just do this

conn.Execute mySQL, lRecs, cmdText

' the lrecs is a returning value and the script dosen't continue
' until it is done
' That puts their order in your PPM table
' Then the email is a separate step. You have already collected
' all the stuff from the form so use those parameters to make
' the sp_ call

Just tidy em up so they are ready to go in as parameters

tidySenderName = "M?. Telnia Codemaster"
tidySenderAddress = "telnia@hangten.com"
tidyRecipientName = Request.Form("FirstName") & Request.Form("LastName")
tidyRecipientAddress = Request.Form("Email")
tidySubject = "RECIPT-Telnia thanks you for your order"
tidyBody = "Hi Mr/Mrs " _
& Request.Form("LastName") _
& " your request for " _
& Request.Form("Order_manual") _
& " has been recieved ...bl;ah blah blah"


Set cmdObj = Server.CreateObject("ADODB.Command")
cmdObj.ActiveConnection = conn
cmdObj.Text = '{call sp_SMTPMail(tidySenderName, tidySenderAddress, tidyRecipientName, tidyRecipientAddress, tidySubject, tidyBody, tidyBody);}
cmdObj.Execute RecordsAffected

'RecordsAffected Optional. A Long variable to which
'the provider returns the number of records that the
'operation affected. If I remember correctly on a
'non recordset returning command it goes to a
'specific value (??-1??) then you'd check that
'value and redirect or response with "thanks for
'your order an email has been sent"

All this would be wrapped in a commitrans on the one and only conn object I believe. Cause you don't want their info without sending them an email.

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

Edited by - Sitka on 08/02/2002 14:00:16
Go to Top of Page

Michael1047
Starting Member

USA
3 Posts

Posted - 08/05/2002 :  15:14:42  Show Profile  Visit Michael1047's Homepage  Reply with Quote
Team,
One problem with sp_OACreate is that it requires the System Administrator's fixed server role in order to run. Most DBA's say, "NO WAY!"

Our team has found a way to send nice looking HTML formatted email via SMTP.

Here are some hints for now, it sounds complicated, but really isn't and it's EXTREMELY FAST:

1) create a stored proc for sending the mail. sp_Sendmail for instance.

2) Create a proc that will create the html document. Use UNION ALL to concat records together. call it sp_CreateHTML

3) In sp_Sendmail, develop a way to generate a unique file name. I used a timestamp with a .html extension. store it in a variable. Use master..xp_cmdshell to make a call to BCP to execute sp_CreateHTML and pass the unique file name as an output parameter.

Here's what happens. BCP (Bulk Copy Program) will execute sp_CreateHTML and output the contents the unique filename. Basically, it generates an html file.

The next step in sp_SendMail will again use xp_cmdshell; however, this time the call will be to Blat.exe. Blat.exe is an command line SMTP mail agent. It's easy to use. When you find the source code for Blat, you will have to change the MIME header to the allow a parameter so you can specify the mail is HTML. I have a port of this and can send it out to you; however, it's not very well documented.

This method of sending HTML mail is the fastest method of sending mail known to the SQL Server industry. I hope to post additional information on this topic soon, it really rocks.

--
Mike
www.4segway.biz







Michael
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000