Sending email in a stored procedure with MSSQL 2000 under Windows Server 2003 (up-to-date SPs)

Posted on 2007-07-26
Last Modified: 2008-03-05
Our server runs Windows Server 2003 Standard Edition with SQL Server 2000 with the most recent service packs. We have IMAIL installed but not Exchange Server nor Outlook.

I am inexperienced in the following issue (re: email and stored procs) so please be as clear as possible in answering this: what is the easiest way that we can send an email from a stored procedure? Thank you very much. A simple example would be much appreciated
Question by:roricka
    LVL 19

    Expert Comment


    IF EXISTS (      SELECT NAME FROM sysobjects
                      WHERE name = 'sp_SMTPMail' AND type = 'P')
                DROP PROCEDURE sp_SMTPMail
    --== 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='',
    --==                  @RecipientName = 'Someone', @RecipientAddress = '',
    --==                  @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
    --== Created Date      -        Thursday, July 19, 2001
    --== Created by            -        Damian Maclennan

    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'
          SET nocount on
          declare @oMail int --Object reference
          declare @resultcode int
          EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
          if @resultcode = 0
                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
          SET nocount off

    Author Comment


    Thank you thank you. But...

    I created the sp and executed it. It didn't send any email, so I added a print statement and I see I'm getting error -2147221005 (Invalid class string) from the line

    sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

    Do I need smtpsvg.dll here? (Honestly, I know just enough to be dangerous here!)

    Thank you for the help so far. Can you get me over this hump?



    How do I debug this thing? Is there a log I can look at? Does this thing use IMAIL? (W/o wishing to sound like I understand more than I do, what is the default email client here?)
    LVL 19

    Expert Comment

    create  a stored procedure called sp_SMTPMail

    then open query analyzer and paste this

    exec sp_SMTPMail
    @RecipientName = 'Someone',
    @RecipientAddress = '',
    @Subject='SQL Test',
    @body='Hello, this is a test email from SQL Server'

    Author Comment

    Hi weellio,

    Hmmm...well, that's just about exactly what I already did. But I did it again, exactly using your text (except for the addresses, of course). Same results. No email is being sent. And when I look inside the sp at what may be going wrong, I see an error code is being generated, as I described above.

    Any ideas would be much appreciated. Remember, this is MSSQL Server 2000. Neither Outlook nor Exchange are running on the server.
    LVL 19

    Accepted Solution

    crap, sorry, yea that does require and external COM component from here

    i'm a little slow sometimes.  this should answer all your questions..
    FYI it does require IIS to be installed on the sql server.

    or here is you want to use sql mail

    Author Comment

    You know what, I found this:

    and got it working (it was a bit tricky, requiring tweaks to the settings for the IMAIL running on the server and using the local IP for @server when calling their extended stored proc. -- Y'have to install a .dll as well.)

    But I will look at what you sent and thank you for trying to help.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now