Managing Database Mail using Microsoft.SqlServer.Management.Smo.Mail managed classes

I'm trying (or, more precisely, failing) to configure Database Mail from managed code using the classes in the Microsoft.SqlServer.Management.Smo.Mail namespace. I can easily retrieve the SqlMail object and create a new MailAccount instance but I can't figure out how to set the name of the mail server. Once the MailAccount is created, the MailAccount.MailServers property returns a single MailServer instance and this is always configured as the local server. In my case the SMTP server used to send email is not on the local server but is located elsewhere in the network. I figure I need to change the name of the MailServer instance, but whenever I try this I always get a FailedOperationException.

I've noticed that the dbo.sysmail_add_account_sp stored procedure allows you to specify the mailserver name when creating an account but the managed MailAccount class doesn't seem to provide this option. Can anyone tell me how to do the set the mail server name with the MailAccount class? If this isn't possible then it seems to me that the SMO features for managing database mail are basically useless.

David

Ok, I'm officially stupid

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. As far as examples of configuring Database Mail goes, there's a single VB sample that illustrates creating a basic MailAccount. Good old 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. It must be possible because Team Foundation Server seems to automatically enable and configure Database Mail when you install it, I just haven't figured out how yet. I'll keep looking.

|||

It appears that you might want to execute the following code:

sp_configure 'Database Mail XPs', 1
go
reconfigure
go

You can do this within SMO using

objDB.ExecuteNonQuery(strSQL)

where objDB is a defined database object on your server. (See my blog entry at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/05/17/21039.aspx for details on mixing SQL and SMO in an application.)

Managing Connections for optimal performance question, switch from Oracle to SQL

I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.

To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.

At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.

Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.No! Definitely not the way to do it. Just use ADO.NET's connection pooling to manage it. Out of the box it's pretty efficient, but you can tweak it if necessary. There's no way you'll write anything that will be as efficient as what's already there.

You're right that a data reader hogs a connection.

And the decision is the same whether you use data adapters or readers.

Don|||How do I use ADO.Net's connection pooling? Is there an article or something that you know about that can teach me to manage this problem better.

Thanks again for your help.|||That connection pooling stuff is pretty cool. My systems class was based on Java so apparantly that type of behind the scenes work didn't take place to efficiently manage the connections. I didn't realize that when I was instanciating a connection it was already managing a pool of connections for me. slick stuff.|||So you figured out how to use it? Cool. Yeah, it definitely is an area where all of Microsoft's hard work is paying off.

Don