July 24, 2002 at 1:20 pm
Hello.
I'm running NT4 SP6, SQL7 SP4.
I am needing to be able to send email via SMTP (can't use SQLMail) and I want to do it via a stored procedure. I've found a script and I've modified it (minimally) and It's not working for me. Can someone please take a look and let me know where I've gone astray?
&&&&&&&&&&&&
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) = '[mail server goes here]'
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', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
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
&&&&&&&&&&&&&&&&
Here's the query I'm using:
exec sp_SMTPMail @SenderName='Kim Morgan', @SenderAddress='kmorgan@schreiner.edu',
@RecipientName = 'My SQL Goddess', @RecipientAddress = 'kmorgan@schreiner.edu',
@Subject='Hello, This is your SQL Server', @body='Hello, this is a test email from SQL Server'
I appreciate any and all help.
Kim G Morgan
July 24, 2002 at 5:38 pm
Save yourself some headache and download a free XP from Gert's site...
http://www.sqldev.net/xp/xpsmtp.htm
--
-oj
Rac v2.1 Public Beta Avail.
October 23, 2002 at 12:04 pm
/*
Here is a Windows 2000 CDO example that I created/use in a stored procedure ...
In Win 2K, CDO is integrated into the operating system, so you don't need to install IIS/configure SMTP server.
On NT 4, I am told by our resident web app developer that there is no way to natively send email without installing IIS and configuring SMTP, or installing a 3rd party component or program.
Installing/configuring IIS with SMTP gives you access to CDO NTS capability. If this is not an option, then you need a 3rd party program. Our Oracle db uses Postie; I've also used WinBatch to create a custom executable that sends mail and use xp_cmdshell to call it.
The other respondant mentions another program - ASPmail is also popular (http://www.serverobjects.com/comp/Aspmail3.htm).
*/
-- declare the variables you will need to support the message
DECLARE
@recip varchar(255),
@subj varchar(255),
@cc varchar(255),
@bcc varchar(255),
@txtmsg varchar(8000),
@htmlmsg varchar(8000)
/*
Fill the variables directly or populate them from a table (ex: Select TOP 1 @txtmsg = COL1, @recip = COL2 ... From EMAIL_MSG_TAB)
*/
Set@txtmsg = 'Hello'
Set@recip = 'kmorgan@schreiner.edu'
Set@cc = ''
Set@bcc = ''
Set@subj = 'CDO Test'
/*
Set up variables to support the extended system stored procedures that integrate with
COM objects.
*/
DECLARE @object int, @hr int
EXEC @hr = master.dbo.sp_OACreate 'CDO.Message', @object OUT
IF (@hr <> 0) Begin
EXEC master.dbo.sp_displayoaerrorinfo @object, @hr
Return
END
/*
Since we will send HTML-formatted message content, we set up the html tagging and embed the message text in a <pre> </pre> tag. We can also create a nice footer, too.
*/
Set @htmlmsg =
'<html>
<head>
<body>
<font face="Arial">
<pre>
' + @txtmsg + '
</pre>
<table width="100%" border="0" cellspacing="0" cellpadding="0" height="30">
<tr>
<td bgcolor="#000000" valign="center"><img src="http://msimg.com/m/r/logo/msft/logo.gif" width="92" height="26" border="0" alt="MSN Homepage" title="MSN Homepage" vspace="0" hspace="0"></a></td>
<td bgcolor="#000000" valign="center"><img src="http://msimg.com/m/r/pp-signin.gif" border="0" width="40" height="17" alt="Log In" title="Log In" vspace="0" hspace="0"></td>
</tr>
</font>
</body>
</html>'
EXEC @hr = master.dbo.sp_OASetProperty @object, 'FROM', 'sqlnotifier@my_company.com'
EXEC @hr = master.dbo.sp_OASetProperty @object, 'TO', @recip
EXEC @hr = master.dbo.sp_OASetProperty @object, 'CC', @cc
EXEC @hr = master.dbo.sp_OASetProperty @object, 'BCC', @bcc
EXEC @hr = master.dbo.sp_OASetProperty @object, 'SUBJECT', @subj
EXEC @hr = master.dbo.sp_OASetProperty @object, 'HTMLBODY', @htmlmsg
EXEC @hr = master.dbo.sp_OASetProperty @object, 'TEXTBODY', @txtmsg
EXEC @hr = master.dbo.sp_OAMethod @object, 'SEND'
EXEC master.dbo.sp_OADestroy @object
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply