| | |
| | |
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. |
Anonymous Email over SMTP
with SQL Server 2000
This SP sends an email over SMTP with SQL Server 2000!
It need's enough rights and "CDO for Windows 2000"! |
CREATE PROCEDURE sp_SMTPemail
(
@From as nvarchar(50),
@To as nvarchar(50),
@Subject as nvarchar(255),
@Body as text
)
AS
-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT
-- create the message object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create
the configuration object
-- Configuration Object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com'
-- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)',
25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'
-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config --
set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'
-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
GO
|
|
SQL Example to execute the SP: |
EXEC sp_SMTPemail
'from@example.email.com', 'to@example.email.com',
'Subjecttext','Messagetext' |
| |
| | | | | Report Bad Submission | | | | | Your Vote! | |
See Voting Log | | | | Other User Comments | 5/7/2003 10:20:21 AM:
Great Job! It works excellent...
| 5/8/2003 9:21:35 AM:
es una pena que no sepa vuestro idioma
para expresar muy agradecimiento a
persona que ponen a disposicion lo que
saben...
Gracias
| 5/25/2003 12:54:55 PM:
EMAIL
| 7/8/2003 9:41:28 AM:
hi your product will be very good cause
i like sending bulk mail
| 7/30/2003 1:32:25 AM:Jing Polito
Hello, thanks for sharing this thing...
but it seems like its not working for
me... i already set my SMTP server...
and when I run the sp i get this "The
command(s) completed successfully." but
I got no message (I addressed it to
myself for verification). Thanks for
your time. BTW, am not familiar with
CDO for Windows 2000.
| 9/15/2003 11:34:53 AM:
I was able to run it without any error.
However, no message was received by the
intended recepient. BTW, what is CDO
for Win2K?
| 10/2/2003 9:50:13 AM:
It does not work on my sql2000. If i
use
sp_OAGetErrorInfo to test for
error.I get a type mismatch error after
setting the config property to @config
| 10/8/2003 11:13:14 AM:
Thanks for sharing this
It only work
if you have iis on the same server as
your sql server. It does not use the
smtp server setting at all.
| 12/23/2003 3:25:02 PM:
How can i 'do if i'need call this store
procedure to send a text field in a
table .
| 12/23/2003 3:27:21 PM:
how can i'do if i'need call this store
procedure to send a text field from my
database as a @body parameter, thanks
| | | Add Your Feedback! | Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
| |
|
