CDONTS Email from SQL*Server

  • ckempste

    SSCoach

    Points: 17983

    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?

    CREATE PROCEDURE SendMail_sp (@FROM NVARCHAR(255), @TO NVARCHAR(255), @SUBJECT NVARCHAR(255), @BODY NVARCHAR(4000)) AS

    DECLARE @Object int

    DECLARE @Object2 int

    DECLARE @Hresult int

    DECLARE @ErrorSource varchar (255)

    DECLARE @ErrorDesc varchar (255)

    DECLARE @V_BODY NVARCHAR(4000)

    DECLARE @hr int

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

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

    IF @Hresult = 0 begin

    --SET SOME PROPERTIES

    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

    --CALL SEND METHOD

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

    --DESTROY THE OBJECT

    EXEC @Hresult = sp_OADestroy @Object

    end

    else begin

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

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

    end

    GO


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

  • Leon Platt

    SSCommitted

    Points: 1921

    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.

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    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

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Andy Warren

    SSC Guru

    Points: 119694

    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.

    Andy

  • ckempste

    SSCoach

    Points: 17983

    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! 🙂

    Cheers

    Chris


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

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    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

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • ckempste

    SSCoach

    Points: 17983

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

    http://tech.dimac.net/

    CREATE PROCEDURE SendMail_sp (@PFROM NVARCHAR(255), @PTO NVARCHAR(255), @PSUBJECT NVARCHAR(255), @PBODY NVARCHAR(4000)) AS

    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 = '163.232.xxx.xxx'

    set @sendername = ''

    set @sender = @PFROM

    set @recipient = @PTO

    set @subject = @PSUBJECT

    set @mailbody = @PBODY

    /*

    Stored procedure using Dimac w3 JMail by Mats Cederholm, mats@globalcom.se, 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

    begin

    select @output = ' Source: ' + @source

    print @output

    select @output = ' Description: ' + @description

    print @output

    end

    else

    begin

    print ' sp_OAGetErrorInfo failed.'

    return

    end

    /*

    Kill the object

    */

    exec @hr = sp_OADestroy @object

    GO

    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.

    .Send

    End With

    EXIT_FUNCTION:

    'Clean up objects.

    Set Configuration = Nothing

    Set Fields = Nothing

    Set Message = Nothing

    SendMessage = Result

    Exit Function

    ERR_HANDLER:

    Result = Err.Number

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

    GoTo EXIT_FUNCTION

    End Function


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

  • Bryan Murtha

    SSC Enthusiast

    Points: 135

    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 8 (of 8 total)

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