locked
Scheduling simple cdosys email task does not work RRS feed

  • Question

  • I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis.  The email is sent using an activex script in a "SQL 2000 DTS Package Task".  When executed manually, the email is sent ok.   When scheduled (and run under our SQL agent account), it fails.  Can anyone point me in the right direction?  Is this a permissions issue?

    '-- this script seems to cause problems, but only when scheduled --
        dim mailer
        set mailer = CreateObject("CDO.Message")
        dim cdoconfig
        const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
        const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
        set cdoconfig = CreateObject("CDO.Configuration")

        with mailer
            set .Configuration = cdoconfig
            .BodyPart.charset = "unicode-1-1-utf-8"
            .BodyPart.ContentTransferEncoding = "quoted-printable"
            .Fields("urn:schemas:httpmail:importance").Value = 2
            .Fields.Update 

            .Subject      = "Notification"
            .From         = "donotreply@test.com"

            .TextBody   = "TEST"
            .Bcc           = "someone@test.com
    "
            .Send
        end with
    '-------------

    Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

    Any help would be appreciated.

    Monday, February 26, 2007 9:29 PM

All replies

  • Almost certainly permissions. Here's the KB article on this subject:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

    Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

    -Doug

     

    Tuesday, February 27, 2007 7:46 PM
  • Thanks for the article.
    We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level. 
    We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password).  As for method 4, we do have package configuration files that have been set as well.

    Also, our SMTP server allows anonymous connections.

    However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps?  We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck.  It still runs only manually, but fails for CDOSYS when scheduled.

    Friday, March 2, 2007 2:18 AM