Configuring Sql Server 2005 DatabaseMail using Microsoft.SqlServer.Management.Smo.Mail classes
Recently I've been working on the installation routine for the next release of our Cognitio Search product. I've been working on some code to configure the Database Mail features of Sql Server 2005 from managed code using the classes in the Microsoft.SqlServer.Management.Smo.Mail namespace. For a while this completely fooled me. Once I had a Server object instantiated for the Sql Server I'm configuring it was easy to retrieve the SqlMail object and create a new MailAccount instance. The problem was that the MailAccount instance didn't refer to the correct SMTP server but always referred to the (local) server on which the Sql Server instance is installed. In my case the SMTP server used to send email is not on the local server but is located elsewhere in the network so I figured I needed to change the name of the MailServer instance, but whenever I tried this using hte Name property (which apparently has a mutator) I kept getting a FailedOperationException. Id noticed that the dbo.sysmail_add_account_sp stored procedure allows you to specify the SMTP server name when creating an account, but the managed MailAccount class doesn't seem to provide the same facility.
After spending the best part of an afternoon reading the documentation I finally figured out the answer. When a new MailAccount instance is created a default MailServer instance is created and added to the MailServers collection of the MailAccount instance. The MailServer class has a Rename method which allows you to change the name of the MailServer to the name of the SMTP server you want to use, but it's not actually possible to set this directly when you create the MailAccount. Quite why you can't construct an instance of the MailServer class and add it to the MailAccount.MailServers collection is beyond me?
I have to say that the SMO documentation in Sql Server 2005 Books Online / MSDN Library really sucks. I've done quite a bit of development with SMO now and it always seems to have a really convoluted way of doing what you want to do. I know there's a tension between the object-oriented and relational/database world, but SMO does really feel like OO bent as much as possible to make it familiar to the (SQL) database developer's expectationsm and this isn't helped by the awful (lack of) documentation. As far as examples of configuring Database Mail goes, there's a single VB sample that illustrates creating a MailAccount by calling the MailAccount constructor. Good ol' Google isn't much help either as there appears to be very little documentation out there other than what's in MSDN Library (or if there is, my afternoon of Googling didn't find it)
Anyhow, I finally got it working so all I need to do know is figure out how to programmatically enable Database Mail on the Sql Server instance that our product is being installed on. By default Database Mail is not enabled and has to turned on using the Sql Server Surface Area Configuration Tool or the wizard in Sql Server Mangement Studio. That's not a painless installation experience for my users so I want something a bit more automatic. I'm assuming it's possible because Team Foundation Server seems to automatically enable sending mail alerts when it installs and I figured it probably uses Database Mail. Stupid me, having checked it again it appears that Team Foundation Server doesn't use Database Mail at all. Oh well, I'll keep looking for a way...
-------
David
del.ico.us | 
Digg It | 
Technorati | 
StumbleUpon | 
Furl | 
reddit