| Author |
 Topic  |
|
scootermcfly
Yak Posting Veteran
USA
66 Posts |
 Posted - 01/03/2002 : 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
India
25 Posts |
Posted - 01/05/2002 : 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
USA
3246 Posts |
Posted - 01/05/2002 : 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 - 01/16/2002 : 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
USA
3246 Posts |
Posted - 01/18/2002 : 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 - 01/24/2002 : 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 Posts |
Posted - 03/07/2002 : 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 Posts |
Posted - 04/01/2002 : 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 - 04/18/2002 : 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 - 04/19/2002 : 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 - 05/04/2002 : 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
USA
4128 Posts |
|
|
amolgokhale
Starting Member
3 Posts |
Posted - 05/05/2002 : 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 Posts |
Posted - 05/06/2002 : 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 Posts |
Posted - 05/30/2002 : 16:01:20
|
Why my varchar(8000) are stripped to 6000 when i pass it to the sp_OAMethod?
|
|
|
|
KHeon
Posting Yak Master
USA
135 Posts |
Posted - 06/05/2002 : 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
USA
571 Posts |
Posted - 07/29/2002 : 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 Posts |
Posted - 07/31/2002 : 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
|
|
|
|
Sitka
Aged Yak Warrior
USA
571 Posts |
Posted - 08/02/2002 : 13:41:12
|
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 |
|
|
|
Michael1047
Starting Member
USA
3 Posts |
Posted - 08/05/2002 : 15:14:42
|
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 |
|
|
|
Topic |
|
|
|
Article: Sending SMTP Mail using a Stored Procedure
