CDONTS Email from SQL*Server

  • Hi all

    I ripped this nice piece of code some time back from the sql*server magazine forum for sending emails via cdonts. All works fine and dandy, but after much effort I cant set the properties to change its default SMTP server it uses. Can anyone assist me?


    DECLARE @Object int

    DECLARE @Object2 int

    DECLARE @Hresult int

    DECLARE @ErrorSource varchar (255)

    DECLARE @ErrorDesc varchar (255)


    DECLARE @hr int

    DECLARE @src varchar(255), @desc varchar(255)

    EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT

    IF @Hresult = 0 begin


    SET @V_BODY = '' + @BODY

    EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM

    EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO

    EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT

    EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY


    EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL


    EXEC @Hresult = sp_OADestroy @Object


    else begin

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • I don't think you can do it through the NewMail object. Assuming you are using CDONTS with IIS server: you can setup the default smtp server with Internet Information Services. Right click on Default SMTP Virtual Server and fill in the info on the general tab.

  • I don't believe CDONTS supports changing of the SMTP Server. I'm looking at the object model right now to refresh my memory and I don't see anything in relation to setting a server. In IIS if you use CDONTS, it's important that you have the SMTP services running on the IIS box as well. You're probably going to have to go 3rd party. I know there are some 3rd party products out there which do support configuring for a particular server. Any particular reason you can't use xp_sendmail except for maybe the From address?

    K. Brian Kelley

    K. Brian Kelley

  • CDO does support this from what I can find on MSDN using a configuration object. Have to admit Im not entirely clear on the differences between CDO and CDONTS! Still, might be worth looking in to.


  • Hi guys

    CDONTS was the original SMTP email library used with Windows NT, then 2000 saw CDOSYS.DLL, has a whole range of changes, including the use of a 'configuration' method that allows you to set smtpserver etc by specifiying a ADODB parameter with all the connect properties in it (for the life of me I couldnt work out now to massage the ADODB property under SQLServer via sp_OAxx)

    Anyhow, I got a programmer to whip up a small DLL that used CDOSYS. Unfortunatly, our dev and test servers worked perfectly, but our new prod box is in a private nework and enclosed by a super tight firewall. All ports were opened for smtp from the server but the CDO libraries refuse to work. I tried Jmail (again, will post the sight) that used its own DLL and apart from taking over 1min to send a mail from the prod server, it worked a treat!? (Jmail on our dev and test servers runs in under 1sec)

    Im still at a total loss in how CDO is actually communicating over the network to the smtp server and it seems to going through the local servers SMTP service as I have "bad" emails in its bad folder. This is strange as im not even specifying this as the relay server.

    The only other issue I can think of is DNS. Apparently its not setup properly on the prod network, so this have something to do with it. Even so, we simply use IP's rather than hostnames.

    So, fun for all! 🙂



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Prior to Windows 2000:

    CDO Library, CDO Rendering Library - for MS Exchange Servers

    CDO for NTS - for an SMTP Server (running locally)

    As for Windows 2000, CDO for Windows 2000 is supposed to be able to communicate with a non-local server (I haven't tried it) using SMTP, so I'm as puzzled as you are Chris. Has anyone sniffed for packets to see if the server is even trying to communicate via SMTP? Has anyone run a netstat -a to see if the server opens a port to communicate with the specified SMTP server? I'd try those things as well to try and help troubleshoot.

    K. Brian Kelley

    K. Brian Kelley

  • Thanks for the info Brian..

    Here is the code and web-site for the 3rd party (free!) dll that works. Ive replaced the stored proc example header to suit some of our internal requirements...


    DECLARE @sender varchar(100)

    DECLARE @sendername varchar(100)

    DECLARE @serveraddress varchar(255)

    DECLARE @recipient varchar(255)

    DECLARE @recipientBCC varchar(200)

    DECLARE @recipientCC varchar(200)

    DECLARE @attachment varchar(100)

    DECLARE @subject varchar(255)

    DECLARE @mailbody varchar(8000)

    set @recipientBCC = ''

    set @recipientCC =''

    set @attachment =''

    set @serveraddress = ''

    set @sendername = ''

    set @sender = @PFROM

    set @recipient = @PTO

    set @subject = @PSUBJECT

    set @mailbody = @PBODY


    Stored procedure using Dimac w3 JMail by Mats Cederholm,, Global Communications WWW AB

    Sending email by instantiating w3 JMail instead of SQL mail.



    Declares variables for input/output from w3 JMail and errormessage


    declare @object int,

    @hr int,

    @rc int,

    @output varchar(400),

    @description varchar (400),

    @source varchar(400)


    Set all values to w3 JMail needed to send the email


    exec @hr = sp_OACreate 'jmail.smtpmail', @object OUT

    exec @hr = sp_OASetProperty @object, 'Sender', @sender

    exec @hr = sp_OASetProperty @object, 'ServerAddress', @serveraddress

    exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient

    exec @hr = sp_OASetProperty @object, 'Subject', @subject

    exec @hr = sp_OASetProperty @object, 'Body', @mailbody


    Set some more values, depending on the value of the variables


    if not(@attachment='')

    exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment

    print @attachment

    if not(@recipientBCC='')

    exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC

    if not(@recipientCC='')

    exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC

    if not(@sendername='')

    exec @hr = sp_OASetProperty @object, 'SenderName', @sendername


    Call execute to send the email


    exec @hr = sp_OAMethod @object, 'execute', NULL


    Catch possible errors


    exec @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT

    if @hr = 0


    select @output = ' Source: ' + @source

    print @output

    select @output = ' Description: ' + @description

    print @output




    print ' sp_OAGetErrorInfo failed.'




    Kill the object


    exec @hr = sp_OADestroy @object


    Also the VB code from our test DLL using CDO that doesnt work on our prod sql*server...

    Option Explicit

    Private mLastErrorDescription As String

    Public Property Get LastErrorDescription() As String

    LastErrorDescription = mLastErrorDescription

    End Property

    Public Property Let LastErrorDescription(ByVal NewValue As String)

    mLastErrorDescription = NewValue

    End Property

    Public Function SendMessage(ByVal ToAddress As String, _

    ByVal FromAddress As String, _

    ByVal SubjectText As String, _

    ByVal BodyText As String, _

    ByVal Server As String) As Integer

    'Simple function for sending email from an SQL Server stored procedure.

    'Returns 0 if OK and 1 if FAILED.


    Dim Result As Long

    Dim Configuration As CDO.Configuration

    Dim Fields As ADODB.Fields

    Dim Message As CDO.Message

    On Error GoTo ERR_HANDLER

    'Initialise variables.

    Result = 0

    'Set the configuration.

    Set Configuration = New CDO.Configuration

    Set Fields = Configuration.Fields

    With Fields

    .Item(CDO.CdoConfiguration.cdoSMTPServer) = Server

    .Item(CDO.CdoConfiguration.cdoSMTPServerPort) = 25

    .Item(CDO.CdoConfiguration.cdoSendUsingMethod) = CdoSendUsing.cdoSendUsingPort

    .Item(CDO.CdoConfiguration.cdoSMTPAuthenticate) = CdoProtocolsAuthentication.cdoAnonymous

    End With

    'Create the message.

    Set Message = New CDO.Message

    With Message

    .To = ToAddress

    .From = FromAddress

    .Subject = SubjectText

    .TextBody = BodyText

    Set .Configuration = Configuration

    'Send the message.


    End With


    'Clean up objects.

    Set Configuration = Nothing

    Set Fields = Nothing

    Set Message = Nothing

    SendMessage = Result

    Exit Function


    Result = Err.Number

    LastErrorDescription = "Number [" & Err.Number & "] Source [" & Err.Source & "] Description [" & Err.Description & "]"


    End Function

    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • I ran this stored proc and got the following does anyone know why?

    Error Occurred Calling Object:  CDO.Message.1 The "SendUsing" configuration value is invalid.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply