Sending SMTP mail via SQL

  • 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

  • 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.

    http://www.rac4sql.net

  • /*

    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