cdosysmail works for a few days then stops

  • I am having an issue with cdosysmail. I am using this to send me an alert to tell if a SQL Agent job has completed successfully or unsuccessfully. After the SQL server is started these alert emails tend to work for about two days. After that I stop receiving them. The error message I get from the agent history when I no longer receive them is:

    Source: CDO.Message.1 [SQLSTATE 01000] (Message 0) Description: The "SendUsing" configuration value is invalid. [SQLSTATE 01000] (Message 0).

    If the SQL Server service is restarted I will start to receive the email messages again after the agent runs for about two days after that the email messages stop and I start receiving the error above again until its reset.

    Has anyone experienced this or have any ideas?

  • Is is safe to assume your using Standard or Enterprise Edition since you said you have SQL Agent running? i.e. that you are not using Express Edition?

    If so, then I would recommend using Database Mail instead of cdosysmail.

    How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (6/13/2012)


    Is is safe to assume your using Standard or Enterprise Edition since you said you have SQL Agent running? i.e. that you are not using Express Edition?

    If so, then I would recommend using Database Mail instead of cdosysmail.

    How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio)

    I'm getting a yellow screen on that particular link.

    Also, why would you recommend using "Database Mail" instead of CDOSysMail?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/13/2012)


    Also, why would you recommend using "Database Mail" instead of CDOSysMail?

    One of the primary reasons is to make sending email from the database an asynchronous operation. When send an email using Database Mail you're actually adding a message to a Service Broker queue. The work of actually trying to deliver that email to an SMTP relay is done later.

    Ancillary benefits include not having to install additonal components onto the databse server and avoiding the use of OLE Automation procs, possibly allowing you to leave the, disabled on the instance.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (6/13/2012)


    Jeff Moden (6/13/2012)


    Also, why would you recommend using "Database Mail" instead of CDOSysMail?

    One of the primary reasons is to make sending email from the database an asynchronous operation. When send an email using Database Mail you're actually adding a message to a Service Broker queue. The work of actually trying to deliver that email to an SMTP relay is done later.

    Ancillary benefits include not having to install additonal components onto the databse server and avoiding the use of OLE Automation procs, possibly allowing you to leave the, disabled on the instance.

    If you consider leaving OLE Automation procs off a benefit, then yes, I agree. Of course, that all falls into whether you have a properly locked down system or not.

    I need to double check on the asynchonus part.

    One advatage that I've found with CDOSysMail is that (according to the infrastructure team where I work) you don't have to make any special setup in a clustered envirionment like you do with Database Mail. To be absolutely honest, I trust those folks and have not done a deep dive to find out what they meant. I'll try to find out more on that subject.

    Another advantage is that I've found that Database Mail won't necessarily send to the outside world (send alerts to my private email, which I check more often, and to certain support vendors) whereas CDOSysMail will send to anyone you need it to.

    Of course, I love for each proc to be able to fill out the FROM on the email without me having to setup a special config for each proc.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you consider leaving OLE Automation procs off a benefit, then yes, I agree. Of course, that all falls into whether you have a properly locked down system or not.

    The lock down is only one aspect of it. Given its bad reputation and the fact that its disabled by default its not an option in some corporate environments. That is not a commentary on the tool itself, rather on the people that have misused or abused it. I prefer not to have it available in case any of these people happen to make their way into my environments.

    I need to double check on the asynchonus part.

    An interesting side note on this behavior. One thing that frustrated me when using cdosysmail long ago was if the mail server was down the email attempt would fail. This meant that I either needed to build an email infrastructure to queue my messages and send them later, i.e. develop something similar to Database Mail myself, or take the risk of a process erroring out because an SMTP relay was not available for one reason or another. Personally I welcomed Database Mail with open arms.

    One advatage that I've found with CDOSysMail is that (according to the infrastructure team where I work) you don't have to make any special setup in a clustered envirionment like you do with Database Mail. To be absolutely honest, I trust those folks and have not done a deep dive to find out what they meant. I'll try to find out more on that subject.

    Database Mail is cluster aware so maybe they have to give it special attention to set it up as a clustered service. They still have to install the cdosysmail COM object and configure the SMTP relay, and on all cluster nodes. I am not sure how they could be getting around that one. They may have made it part of an OS image they use as a base install, but it had to be configured somewhere. You can essentially get to the same place with Database Mail using a T-SQL script that can be run when the instance is brought online. I have one that is part of my base install steps. The script is setup to be runnable against any instance. I guess it depends what people think are easier, for them, but I myself have not known Database Mail to be a PITA and never had an issue with it, although I have not administrated it in a clustered environment. For the two clusters I've worked on, I did not have to mass with Database Mail.

    Another advantage is that I've found that Database Mail won't necessarily send to the outside world (send alerts to my private email, which I check more often, and to certain support vendors) whereas CDOSysMail will send to anyone you need it to.

    That is most likely a limiting configuration in your SMTP relay, or somewhere downstream. Database Mail has no such restriction. Not only does it not have an issues sending email to outside addresses, you can configure Database Mail to use an outside SMTP relay as well, e.g. to use your Gmail or Live account so for testing from home I don;t even need to setup a separate SMTP relay. How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account

    Of course, I love for each proc to be able to fill out the FROM on the email without me having to setup a special config for each proc.

    I agree, that is annoying! In SQL 2005 you are stuck with creating a new profile for each from-address. I was disappointed Microsoft left the ability to override the profile's from-address out of the initial release. They added support for from-address and reply-to-address to Database Mail in SQL 2008.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • All good stuff. I do agree that OLE Automation has a bad reputation through frequent improper use and is also relatively slow when used for things like returning directory information when compared to many other methods. Of course, I don't use OA for those types of things. I also understand that you have to live within the confines of what a company will actually allow.

    I cannot personally speak as to whether or not Database Mail is a PITA to setup on clusters because, like you, I wasn't the one that set it up on the clustered servers that I'm working on. I can only go by what those that set it up say. I still have a deep dive to do to determine if what they say is true or not. I trust them and I trust you and there's a conflict between what all of you say. For me, the jury is still out until I can find the time to find out for myself. With any luck at all, maybe I can teach "them" a thing or two.

    Shifting gears, you said you had problems "years ago" with CDOSys Mail. Was it actually CDOSys or was it its predecessor (the name eludes me just now but the name still started with "CDO"). I'm wonder if maybe CDOSys fixed some of the problems its predecessor may have had.

    Thanks for the link on setting up Database Mail to use a remote mail server. That'll make it a whole lot easier to do experiments with from home.

    Shifting gears again, I've never had to install CDOSys (or its predecessor) separately. Its always been installed as a part of SQL Server.

    Last but not least, I very much appreciate and agree with the fact that a lot of functionality defaults to being turned off to "improve" security. But, and I think you agree, I also think that's because of the incredible misuse that some of these features have gone through at the hands of the unaware over the years. xp_CmdShell and OA are both good examples. There are a lot of people who don't know how to properly secure a system (even without enabling such functionality) so that you can use these marvelous tools safely. Even MS flubbed up a bit when they made it possible for non-SA logins/users to use xp_CmdShell directly through the use of a proxy account. I'm a strong believer that non-SA folks should never have the ability to run xp_CmdShell directly but I'm also a strong believer that they should be able to execute a stored procedure that uses it. As you know from a thread long past, there are some pretty simple methods to doing that properly.

    Since I'm still a bit stuck in the world of 2005 (that's all they currently have at work), I'm really happy to hear that they've made it so you can override the "FROM" address in Database Mail. That's going to make my life a wee bit simpler. Now all I have to do is get the infrastrucure folks at work to install it on the non-production (Dev, Test, etc) clustered servers at work (yep... even those are clustered) so that I don't have to maintain a CDOSys work around for testing procs that send mail.

    As always, I really enjoy these conversations with you, Orlando. They bring out some good points where not only I learn something in the process, but I think others will, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/15/2012)


    I cannot personally speak as to whether or not Database Mail is a PITA to setup on clusters because, like you, I wasn't the one that set it up on the clustered servers that I'm working on. I can only go by what those that set it up say. I still have a deep dive to do to determine if what they say is true or not. I trust them and I trust you and there's a conflict between what all of you say. For me, the jury is still out until I can find the time to find out for myself. With any luck at all, maybe I can teach "them" a thing or two.

    I am not sure I meant to pit my word against theirs. They may have challenges configuring Database Mail in a (or their specific) clustered environment of which I am not aware. I am only saying I have had no trouble using it on a cluster, or configuring it and using it on a stand-alone server.

    Shifting gears, you said you had problems "years ago" with CDOSys Mail. Was it actually CDOSys or was it its predecessor (the name eludes me just now but the name still started with "CDO"). I'm wonder if maybe CDOSys fixed some of the problems its predecessor may have had.

    Hmmm, were you thinking of CDONTS? I may have used that years ago but I couldn't say for sure. The setup to which I was referring uses sp_OA and CDOSYS to send email from SQL 2005 Standard on Windows 2003 x86 and is in service on one instance in the current shop. I converted everything else to Database Mail in the current shop but had to leave CDOSYS in place on the one instance because of the From-address limitation in SQL 2005 Database Mail (did I mention it is annoying they left that feature out? :-)). This is why I know the From-address limitation is not present on 2008 as that is what the 2005 instance is migrating to (soon). CDOSYS will not be used and the sp_OA procs will be left disabled post-migration to the 2008 environment.

    Thanks for the link on setting up Database Mail to use a remote mail server. That'll make it a whole lot easier to do experiments with from home.

    Anytime. Happy to assist.

    Shifting gears again, I've never had to install CDOSys (or its predecessor) separately. Its always been installed as a part of SQL Server.

    I just looked it up (here[/url]) and CDOSYS is included on Windows Server 2003 so your team really doesn't have any setup to do as long as your procs supply the mail server IP/hostname. It is something you have to explicitly add to Windows 2000 which is what I may have been remembering. Sorry for any confusion there.

    Last but not least, I very much appreciate and agree with the fact that a lot of functionality defaults to being turned off to "improve" security. But, and I think you agree, I also think that's because of the incredible misuse that some of these features have gone through at the hands of the unaware over the years. xp_CmdShell and OA are both good examples. There are a lot of people who don't know how to properly secure a system (even without enabling such functionality) so that you can use these marvelous tools safely. Even MS flubbed up a bit when they made it possible for non-SA logins/users to use xp_CmdShell directly through the use of a proxy account. I'm a strong believer that non-SA folks should never have the ability to run xp_CmdShell directly but I'm also a strong believer that they should be able to execute a stored procedure that uses it. As you know from a thread long past, there are some pretty simple methods to doing that properly.

    No question about it. I may still produce the article we kicked around, showing how to properly secure xp_cmdshell with detail about a real-world implementation of the technique. You said you had lost interest because of similar efforts, or potential holes you thought people would poke in it, but I would take it on from a how-to perspective. If you are still not interested in producing it, if I produced it, would you be willing to do a technical review on it? My first SSC article is scheduled to be published 6/28 so I am very excited about that. My second article, a followup to my first using a third-party tool to accomplish the same task, is in the works.

    Since I'm still a bit stuck in the world of 2005 (that's all they currently have at work), I'm really happy to hear that they've made it so you can override the "FROM" address in Database Mail. That's going to make my life a wee bit simpler. Now all I have to do is get the infrastrucure folks at work to install it on the non-production (Dev, Test, etc) clustered servers at work (yep... even those are clustered) so that I don't have to maintain a CDOSys work around for testing procs that send mail.

    Not sure how much DB admin work you do on a daily basis versus working with data or development but you may find the sysmail tables in msdb that support Database Mail quite handy for troubleshooting mail issues, or simply tracking email that was sent. Database Mail, given that it is queue driven, also has failed-email retry capabilities and email-send timeout thresholds built-in making it a bit more reliable that CDOSYS out of the box.

    As always, I really enjoy these conversations with you, Orlando. They bring out some good points where not only I learn something in the process, but I think others will, as well.

    As do I. Our exchanges have taken on quite the adversarial feel to them at times but I have always appreciated and respected your approach, and your patience. You have no doubt rounded down some of the sharp corners on my views and made me a better database professional, and I thank you for that.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Speaking of "sharp corners", it's a funny thing about adversity and the written word. Although I agree with the author of the following post, how should one take the word "ridiculous"? Instead of offending the one who wrote about the subject of that post and possibly coming across as making an ad hominem attack on the person whose idea it was, there could have been a better way. Terms like "ridiculous" are inflamatory at their best even when it is accurate. 😉

    http://www.sqlservercentral.com/Forums/FindPost1316654.aspx

    Shifting gears... yes, if you'd like to write an article on the real world example of the use of xp_CmdShell, I'd be happy to do the technical review on it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/16/2012)


    Speaking of "sharp corners", it's a funny thing about adversity and the written word. Although I agree with the author of the following post, how should one take the word "ridiculous"? Instead of offending the one who wrote about the subject of that post and possibly coming across as making an ad hominem attack on the person whose idea it was, there could have been a better way. Terms like "ridiculous" are inflamatory at their best even when it is accurate. 😉

    http://www.sqlservercentral.com/Forums/FindPost1316654.aspx

    Shifting gears... yes, if you'd like to write an article on the real world example of the use of xp_CmdShell, I'd be happy to do the technical review on it.

    Thanks, and thanks.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Guys ... Tell the solution of the original question?

  • If you're on SQL 2008 implement Database Mail.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks SSCertifiable

    Unforgettably, we are still stuck with SQL Server 2005 for next 6 months. Is there any resolution of this issue in SQL 2005.

    Cheers

  • No problem Grasshopper 🙂 Database Mail is available on SQL 2005 but there is a limitation when it comes to setting the from or reply to addresses dynamically. If that is not an issue for you then I would switch to Database Mail.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • That's the main reason we are using cdosysmail in once of our project. Database mail doesn't do this. We have plans to move to database mail once we get SQL 2008.

    In the mean time do we have any solution for this bug ?

Viewing 15 posts - 1 through 15 (of 16 total)

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