Do you find the Support WebCast transcripts helpful?
Let us know!

Note This document is based on the original spoken Support WebCast transcript. It has been edited for clarity.

Joseph Pilov: Today we're going to talk about Troubleshooting Microsoft^® SQL Mail. Our presentation was primarily based on the call volumes generated at Product Support Services at Microsoft, and this WebCast will focus on those issues that are most commonly encountered during our troubleshooting of SQL Mail.

So let's start with the agenda today, with the next slide (slide 2). We're going to cover the basic functionality of SQL Mail, what SQL Mail is used for. Then we're going to go over the SQL Mail setup requirements, which is a major part of our discussion today. Our discussion is primarily based on the setup or configuration of SQL Mail. Then we're going to cover some basic troubleshooting steps and some more advanced troubleshooting topics, with how to deal with SQL Mail issues.

We're going to move to the next slide (slide 3), and we're going to start with the overview of SQL Mail services. SQL Mail is a service that comes with Microsoft SQL Server™, and it provides a way to send and receive e-mail messages through the SQL Server. As we will find out, it also provides functionality to send e-mail through the SQL Agent Service, another service that comes with SQL Server.

SQL Mail establishes a client's MAPI connection to a mail server, just like your Microsoft Outlook^® client makes a MAPI connection to a mail server — it's the same way with SQL Mail. And as I said, it comes in two varieties. One is the SQL Mail, which is the service that provides programmability and works with the mail stored procedures available in SQL Server, and the other is the SQLAgentMail, which is the service that is primarily used for notification and alert purposes. We will see that a little bit later.

Moving to slide 4, here we're going to talk about the SQL Mail stored procedures. The stored procedures that are listed are the ones used by SQL Mail: xp_startmail to start your mail; sp_processmail; xp_sendmail; xp_readmail; xp_deletemail, xp_findnextmessage; and xp_stopmail. Basically, SQL Mail is just a number of stored procedures that can be used in your stored procedures or in triggers. Let me just give you an example of how SQL Mail could be used.

You can send an e-mail to SQL Server with a query in the body of the e-mail message. That query will be processed by SQL Server, and the output of the results will be sent back to the original sender. So the output could be sent in an attachment, or it could be sent just in the body of the e-mail message, depending on how you want to get the results back. But that's the kind of functionality that SQL Mail stored procedures provide you.

Again, SQL Mail stored procedures allow you to manipulate the data, process those queries, and return the result sets in a reply mail. Those can be used in stored procedures, triggers, or just batch ad hoc statements, if you would like to do that.

We're going to go to the next slide, slide 5, and the next service we're going to talk about is SQLAgentMail. I want to stress here SQLAgentMail is independent from SQL Mail. It's another service. It doesn't directly interact with SQL Mail, and as the name suggests, it's used by the SQL Agent service. SQLAgentMail only sends messages. It sends notification types of messages and can be used when an alert is triggered. For example, when some performance event is being monitored and an alert is triggered by that event, you can have SQLAgentMail notify the administrator that that performance event had occurred — for example, running out of space for a database. That's one alert that could be triggered, and an e-mail could be sent to the administrator notifying them that a specific database is running out of space.

You can also use SQLAgentMail to report the status of a job that's been scheduled. For example, a very common one is backing up your database on a regular basis. You want to know the status of the backup, of the completion of the job, so after the job completes, you can get SQLAgentMail to send a mail notifying you that the job completed successfully.

We're going to move to the next slide (slide 6), and we're going to cover some basic terminology. Before we continue, we want to make sure that at least the basic terms that we're going to use throughout this presentation are discussed. The first term that we want to cover, on slide 6, is the mail server. The mail server is a necessary component throughout this whole process of setting up SQL Mail. For those who are not familiar with them, mail servers are, for example, Microsoft Exchange, Novell GroupWise, or Lotus Notes. There are a lot of mail servers out there, some that you can download, some that you can purchase from a vendor. Mail servers can be configured in their native format — in other words, in their proprietary supported format. A common protocol used out there is the Post Office Protocol, or as it's known, POP3. And SMTP, the Simple Mail Transfer Protocol, that's the other common, industry-standard protocol. Those are two protocols that manage the incoming and outgoing mail, respectively. The Post Office Protocol manages incoming mail, and SMTP manages the outgoing mail. So the mail server is a component that we will need in the whole scenario.

The next one is the mail client. A mail client is the application that most often sits on your client computer and receives or sends e-mail. Mail client examples could be Microsoft Outlook, Microsoft Outlook Express, Eudora, the Lotus Notes client — there are a number of clients out there. Windows^® Messaging is another one, which comes with Windows NT^®. So there are a number of mail clients out there. For our purposes, we want to make sure that it's a Mail Application Program Interface (MAPI) client, because SQL Mail needs that functionality, it needs that application-programming interface to function properly. So the client needs to be a MAPI client.

Dwayne Lanclos: One of the things to understand when you're dealing with SQL Mail is that we rely on the messaging functionality actually being installed on the machine. So we take advantage of that. We make a MAPI call to it. Just like in Internet Explorer, when you want to send the Web page that's on the screen to somebody, if you do File, Send, Page by e-mail, it then employs whatever mail messaging functionality you have on your server to send that page. SQL Server is pretty much the same way. So if you don't have the mail portion of it working, you're never going to get SQL Mail working.

Joseph: Thanks, Dwayne. We're going to move to slide 7, and we'll continue with some of the terms here. A mail profile is a necessary component for SQL Mail to operate properly. The mail profile is something that you can configure in Control Panel, under the Mail icon, and click the Show Profiles button. The mail profile is a critical component in configuring SQL Mail. And that's where you configure it — Control Panel, double-click Mail, and click Show Profiles.

Default mail clients — when you have a mail client already installed on your client computer, ready to send and receive e-mail, then for the SQL Mail functionality to be available, you want to ensure that the mail client is the default mail client. The way you configure that is you can either do it through Control Panel, Internet Options, and the Programs tab, and then there is a drop-down box for E-mail. And that's where you specify what mail client you want to be the default mail client on that machine. As Dwayne just pointed out, if you want to send an e-mail through Internet Explorer or any other application on your computer, you can select File, Send, Page by e-mail, whatever functionality you're using. That will automatically invoke the default mail client on that machine. And that's the default mail client that we're talking about.

The other place where you'll want to configure it is within Internet Explorer, under Internet Options, and the Programs tab, and again, the drop-down box for E-mail.

There are two other important terms that we're going to be using today, first is the Corporate or Workgroup option. This is an option that's only available with the Outlook client. You will see how this option is being used by SQL Mail and how we need to configure it. The other term is the Internet Mail Only option. Again, it's an Outlook option. It's Outlook specific, and we will see how the Corporate and Workgroup option is the correct option to use when you're using SQL Mail functionality. That's configurable in your Outlook mail client.

Next we're going to go to slide 8, and we're going to cover some of the basic SQL Mail setup requirements. The first thing you do need, as we pointed out, is a mail server. You need that server component, that back end, either on that same machine, or on another machine, or across a LAN, across a WAN if you want — but you do need a backend server that manages incoming and outgoing e-mail. Actually, in some cases, it could be two servers. You could have a server that manages your incoming e-mail, the POP3 server, and you could have an SMTP server that manages your outgoing e-mail. So you need to have one or more servers that manage your incoming and outgoing e-mail.

The next component that you need is a MAPI client, and we need to make the distinction — for SQL Server 7.0, you need Simple MAPI or an Extended MAPI. But for SQL Server 2000, you do need to have an Extended MAPI client.

The next component that's necessary to set up or configure SQL Mail is a Windows NT or Windows 2000 user account that we'll have a profile configured for. That account is also going to be the startup account for your SQL Server. We'll go into a couple of examples later, but you need a Windows NT domain account. It needs to be a domain account if you use an Exchange Server, specifically. You could use a domain or a local NT account for the situations where you're using a POP3 and SMTP server. Again, we'll go over that in a bit to see how the domain account is configured.

A mail profile is the other component that we need. The mail profile is configured for the NT account that we just covered. That, again, is a necessary component for the SQL Mail to function correctly.

Let's go to slide 9. This is a SQL Mail setup checklist. The important point I want to make here is that the order on this slide is very important, and this is why we numbered all of the points here. We need to follow the order to have the SQL Mail configured and working properly. If we miss any of the steps here, then we'll end up with SQL Mail not functioning as we expect it to. With that, I'm going to go into the steps.

The first one is you want to install a mail client, and you want to install that mail client on the machine where SQL Server is running. That's an important thing to note. For example, you need to install an Outlook client on the machine that the SQL Server service is installed and running on. After you do that, you want to log on to that server using the account configured to start the SQL Server service or SQL Agent.

Let's take an example. Let's assume that the startup account for SQL Server and SQL Agent is the SQL Admin account. When you're running on SQL Server, you may be logged on with the Administrator account, for example. But for the purposes of configuring the SQL Mail, you need to log off with the Administrator account and log on with the SQL Admin account, the startup account for SQL Server and SQL Agent.

After you log on with that account, we're ready to go to the next step, and that's the step where we configure a mail profile. You can do that through Control Panel, from the Mail icon, and enable a new profile, providing the server name — be it an Exchange server or a POP3/SMTP server — configure the mailbox, and all of that.

I want to summarize step 2 and step 3 again. You want to log in with the SQL Admin account, as in our example, and you want to configure the profile for that account. If you do not log on with the startup account, the mail profile will not be configured for the correct account and will not be correctly tied to the correct account. And therefore, your SQL Mail will not be able to use that profile correctly.

Dwayne: And just to clarify something, it's not like you have to log in every time. We're just doing this one time, to configure your mail account. And that's the only time that you need to log in with the account that you use to start up SQL Server.

Joseph: That's correct. After you've configured that, you can log in to the SQL Server box with any account that you need.

The next step is to check the mail client used for the profile, and make sure it's the default mail client. SQL Mail will look for the default mail client configured on that machine, and you want to make sure that the mail client is the default mail client.

The next step, after you have configured all of that, is to send and receive mail using that mail client to ensure that the mail functionality is available, that it is properly configured and is working.

After that is configured properly, the next step is to provide the mail profile to SQL Mail and enable it so that SQL Mail can use it. One way to do it is through the Enterprise Manager. You can drill down to the SQL Mail service; you can right-click on it, and you can use the drop-down box that's available, which will look for the available profiles on that machine, and you can select your mail profile and test it at that point.

So with that, you are pretty much done with configuring SQL Mail. After you have the mail client working, after you've configured it correctly, and after you've provided the mail profile to the SQL Mail, your SQL Mail is ready to work.

There are three more steps here. Next you want to start SQL Mail and you want to test any e-mail to make sure that your configuration so far is correct and proper.

The next step is to configure SQLAgentMail, if you are going to use the SQLAgentMail. At this point, you are ready to configure it, so you do something very similar to what you did with SQL Mail. You go to SQL Agent, right-click, choose Properties, and you provide the profile for the SQL Agent to be used. And then you want to start the SQL Agent service and test sending an e-mail to an operator.

With that, we're going to move to slide 10, and here's where we're going to start covering a few basic troubleshooting topics that you'll want to be aware of when you're encountering problems with SQL Mail.

Dwayne: Yes. And if you ever call in with a support issue, we will be asking you these same questions. These are the kinds of questions that we usually ask customers when they call in with a SQL Mail problem.

Joseph: Right. And that's why, as I mentioned at the beginning of our presentation, we wanted to summarize, as much as possible, the kind of questions and the kind of issues that we keep seeing in Product Support Services.

The first thing you want to ask yourself when a mail is not working properly is, is the problem with the mail server itself, with the back end? You want to confirm that that mail server is supported with SQL Mail. And the configurations that are supported are Microsoft Exchange Server and any server configured as a POP3/SMTP server. Third-party e-mail servers running in native mode are not supported, unless they are configured as a POP3/SMTP server; but at that point, they are just appearing as a POP3/SMTP server to the mail client.

One very important thing you'll want to ensure is that there is 100 percent connectivity for incoming mail, and in a lot of cases, for outgoing mail as well, but incoming mail in particular. And we'll see later how that plays an important role. But just briefly, if there is a problem with connectivity any time incoming mail is interrupted, let's say in your Outlook client, for example — you may have experienced this situation, where you open your Outlook client and there is a problem with connectivity between your Outlook client and your incoming mail server or POP3 server, and then you would get a message like, "Mail server is not available. Would you like to retry?" Or something like that. And at that point, you can interact with that dialog box and say "Yes, I want to retry, cancel, or whatever." However, SQL Mail cannot interact with those dialog boxes, and in situations where this kind of dialog box pops up, and it actually pops up in what's called a virtual desktop in the background, which you cannot see, SQL Mail cannot interact, and you can see mail hang situations. But we'll cover that. Dwayne will go into that in a lot more detail later on.

Dwayne: Just as an example of this, somebody would call in and we would ask them, "What mail server are you using?"

"Oh, I'm using Lotus Notes."

We would then ask them, "Do you have that configured to be used as a POP3/SMTP server?"

If they say yes, then we would try to figure out what the problem is. If the server is taken down for maintenance, then that probably would explain why SQL Mail is hanging. So always check to see that the server is a valid server that would work with SQL Mail, and also make sure that it's actually running and that it's not taken down for maintenance or something like that, because that could cause problems with SQL Mail.

Joseph: All right. We're going to go to slide 11, and we're going to discuss the next topic. Now that you've ensured that the server is up and running, it's properly configured, and it's supported with SQL Mail, then you'll probably want to look at your mail client, and you'll want to make sure that it's a supported configuration. As you see on the slide, we need to have Outlook 2000 to work properly with SQL Server 7.0 or SQL Server 2000 Mail, or you need to have Outlook 2002 to work with SQL Server 2000. And again, those two clients support Extended MAPI functionality, which is needed by SQL Server 2000.

One note here is that Outlook 2002 does not work with POP3/SMTP and SQL Mail. And again, we will see why that's the case. Dwayne will discuss this later.

What is not supported is Outlook Express, because when you configure it, it does not create a mail profile. And you do need a mail profile for SQL Mail to run successfully. And therefore, Outlook Express is not a configuration that we support. Any non-MAPI-compliant clients are also not supported. As we mentioned, we do need a MAPI-compliant client to work properly with SQL Mail.

Dwayne: And what we mean by that is, for example, for the same customer we're asking, "What are you using for mail client?"

He says, "Well, I'm using the Lotus Notes client."

That's what we're talking about. If you're using the Lotus Notes client, it might work perfectly fine with Lotus Notes, but as far as we know, it might not be a MAPI-compliant type of client. So in other words, MAPI calls that are made to that client may not be recognized, or something like that.

It might be MAPI compliant, but there might be other problems involved. One thing that we have seen, just to use an example like Lotus Notes, is it would require some type of a password to be entered in when you try connecting the client to the server. If that happens, SQL Mail is being run from a service, so it cannot support any type of interactivity like that for entering a password. So that's why we say that we only support Outlook, for example, as one of the clients, because that's something that we tested. There are so many different third-party clients and servers, and we can't test with them all. So we support what we are able to test with.

Joseph: Right. We're going to go to the next slide, slide 12. Another thing to look at is your Outlook client. We're going to talk about Outlook specifically here — is that client configured with the Corporate or Workgroup option? This is the only option that works with SQL Mail, and you want to make sure that is configured properly in your Outlook client.

Microsoft Outlook works with both Exchange or a POP3/SMTP server. It does not have to be in the Internet Mail Only mode to connect to a POP3/SMTP server. I want to make sure that you understand this. The Internet Mail Only option will not work with SQL Mail. That's pretty much the case. The Corporate or Workgroup and Internet Mail Only options are not available in Outlook XP.

In Outlook XP, a unified interface was created where you do not distinguish between those two options. And when you configure your server, I guess in the background, automatically, the client determines which option it needs to use. And this is one reason why Outlook XP cannot be configured to work with POP3/SMTP mail servers.

Dwayne: Just to add a little note on that, Outlook 98 and Outlook 2000 have both of these options, the Corporate or Workgroup and Internet Mail Only. Many times when a customer is installing these applications, they're saying, and I can understand it, "Well, I'm using an Internet Mail client, I'm using a POP3/SMTP server, so I want to choose the Internet Mail Only." Well, what then happens is it installs a kind of lightweight version of MAPI that doesn't have the full functionality that SQL Mail needs to work with it, because it doesn't need to talk to Exchange servers and things like that. It's only talking to a POP3/SMTP server. So because it doesn't have the full MAPI functionality, that's why SQL Mail won't work with it; so that's why it's critical to install the Corporate or Workgroup mode. Even though you do that, you can still configure it to use with a POP3/SMTP server. So it doesn't mean Exchange only. That's not what Corporate or Workgroup means.

And, as Joseph pointed out, that was unified in Outlook 2002, that was brought together. And when you're using an Exchange server, it uses one portion, and that will work with SQL Mail; but when you try using it with a POP3/SMTP server, then it tries using what I was calling the lightweight version, and that's what doesn't work with SQL Mail.

Joseph: And finally, with the client, again, if you have a problem, you want to make sure that the correct e-mail client is configured as the default e-mail client.

Dwayne: Just one note on the default e-mail client. You know, you might think "Well, yes, Outlook is the default e-mail client, and I didn't do anything to change it," but there are other things that could happen. Routine versions of Internet Explorer being installed on the system might change it, or somebody just happens to click on Outlook Express, and one of the questions that pops up is, "I noticed that I'm not the default e-mail client, do you wish me to be?" If somebody doesn't know the repercussions of that, they might say, "Yes, sure," click on it, and then change it to be the default e-mail client. So that's another thing that we always run through with customers who call in on the phone. Even though they say that they're using Outlook as their mail client, we'll still have them look to see if it's selected as the default mail client.

Joseph: Right. On slide 13, we're going to discuss the startup service accounts. As we pointed out, the SQL Server and SQL Server Agent startup accounts must be Windows accounts, and they cannot be the Local System account. The reason is the Local System account only has privileges to the local machine. It does not have privileges to connect to a mail server or any other server, for that matter, and that's why you do need your startup accounts to be configured. They need to be Windows accounts. As I said, domain accounts were for Exchange Server as a back end, and domain or local accounts for your POP3/SMTP server.

Dwayne: Another reason is what we were talking about earlier with the mail profile. You have to be able to log in with that account. If you set up SQL Server to start with the Local System account, you can't really log in as the Local System account yourself to create a mail profile. If you log in as administrator, that's not the same thing as logging in as the Local System account. So once again, in the past we've seen where the Local System is the SQL Server startup account, but people log in as Administrator and try to set up a mail profile there. If you do that, then when you try to look to assign the mail profile in SQL Mail, you won't see it, because it's associated with the Administrator account, not with the Local System account.

Joseph: Again, for Exchange servers, we do need a domain account. And for a POP3/SMTP server, we can use either a domain account or a local NT account. You want to make sure that the mail profile, again, is associated with that startup account. You need to log in with that startup account and associate that profile with that startup account. Dwayne is going to go over all of the details on that.

We're going to move to slide 14. The next breaking point, in a situation where we're troubleshooting SQL Mail, is whether the mail profile is configured correctly. Again, we cannot stress this enough: you must log on with the service startup accounts to configure the mail profile, for the reason we mentioned above. We need to associate that account with the profile that's going to be used. Another thing you want to do, when you configure a profile, is avoid using special characters such as hyphens (-), pound signs (#), or periods (.). Spaces are allowed in the name, but try to avoid using special characters. It's just a good practice. It's also a good practice to keep the name short. You don't want to make up a 150-character name. I don't even know what the box allows you to configure, but it's a good practice to keep it within 30 characters or so. Again, spaces are allowed in the name. In fact, you can often see that your default profile, when you configure an Outlook profile (that is if you configure it for an Exchange server), is MS_Exchange_Settings. So that's a valid profile.

One other thing that you need to be aware of is if you're using an Exchange server, the NT account that's being used for the profile, and that's been configured to be the startup account, needs to have a mailbox on the Exchange server. We have seen situations where everything else is configured properly. You have a domain account. You have everything configured, but SQL Mail was using an Exchange server, and that NT account did not have a mailbox configured on that Exchange server. That, in some cases, is very obvious, but that's why we want to point it out. In some cases it wasn't that obvious, and we want to make sure that that is cleared up.

The last point here is in the last bullet. There is information on how to configure mail profiles in SQL Server Books Online. It's a good resource. It actually walks you step-by-step, 1-2-3, with what to do next, as far as configuring the profile.

Let's go to slide 15. This step is after you have configured everything and you've checked your profile, you've checked your startup account, and you've checked your server and client, this may seem very obvious, but you want to make sure that you can send and receive e-mail from the mail client, and that it's under the profile that you have configured it for.

You want to make sure that there are no pop-up dialog boxes that appear when you send or receive e-mail, any kind of pop-up boxes. For example, a common thing is you're opening your Outlook client, and the next thing that happens is it asks you to log in to a server, it asks you for a user name and password. That's a common scenario that we've seen. And that's great when you personally interact with the mail client, but SQL Mail cannot provide it, cannot type that log in name and password for that client to connect to the server. So in those cases you would see a hang situation, where SQL Mail is just waiting for some interaction with that dialog box. So any kind of logon or logoff dialog screens when you start up or close your mail client is not something that you want to have. These are any dialog boxes that require an OK or Cancel sort of interaction.

Another common one is there have been security patches that Microsoft issued to prevent some viruses, the ILOVEYOU virus was a common example. Those patches, every time an e-mail is sent, require the user to agree or click Yes, I want to send this e-mail. So there is a dialog box that pops up and says there is a service on this machine or there's an application on this machine that's trying to send e-mail on your behalf, do you want to send this e-mail? You can click Yes or No at that point. Those kinds of security screens are to be avoided in situations where SQL Mail is being configured. Dwayne?

Dwayne: Yes. That security screen is a particularly tricky one to try to catch, because it doesn't happen when you're in the client itself. If you pull up Outlook and you send an e-mail, you're not going to see it there. You only see it when there's some other application that tries using the messaging functionality, that tries making a MAPI call. That's when you'll see the pop-up appear. So just what we talked about earlier, logging in to the client and sending mail, yes, sure, it looks fine, but whenever SQL Mail tries to do it, that's when it causes that security screen to appear behind the scene.

I haven't tested this out myself, but I would think if you could get to another client that uses MAPI, like Internet Explorer or something like that, and then try and send a Web page to yourself, that that would be a non-Outlook application trying to send mail that would cause the security screen to pop-up if you have the security patches installed.

Joseph: Right. Moving on to slide 16, we just want to briefly touch on SQL Mail in a cluster environment, and we want to start with saying that MAPI is not a cluster-aware application programming interface. It was not designed for it to run in a cluster environment, and therefore, it cannot fail over. That's a term that's used in Microsoft Cluster Services. For those who are not familiar, Microsoft Cluster Services is a special service that runs only on Windows NT Enterprise Edition or the Windows 2000 Advanced Server or Windows 2000 Datacenter Server editions of the Windows 2000 Server family. When you configure SQL Server on a clustered environment, only SQL Server 7.0 and 2000 Enterprise Editions can run in a cluster environment.

Moving on to slide 17, we've seen situations where SQL Mail can be configured on a single node. It functions without any problems, as long as everything is configured properly, as we've discussed it up to this point. But when you're in a failover situation where the server moves from the one node to the secondary cluster node, then you may see issues with that. Again, MAPI is not compliant. So there is no guarantee in most cases that the configuration will work. In most cases, the way we approach those kinds of support situations is on a best-effort basis. We try to help our customers as much as we can, but because of the functionality of SQL Mail, the MAPI, we have limited success with it. In some situations, you may be able to configure it on both machines. You may be able to configure mail clients. And one thing that you can try to do is store your .pst file or the mail storage in Outlook on the shared disk, on the shared cluster disk, where either node could access that storage file. Again, in most cases this is on a best effort basis. It's not supported, and we try to help our customers as much as we can with those situations, being aware of the limitation.

At this point I will hand it over to Dwayne, who will continue with the advanced troubleshooting topics.

Dwayne: Okay. Turning over to the next slide, slide 18, here are some of the frequent problems that we've gotten in the past.

Somebody might call in and say mail is stuck in the Outbox or the Inbox, and we'll be talking about this in more detail, as to what are the situations that can cause this to happen, and what you can do about it.

Another typical problem we get is SQL Mail hangs. That is very, very common, and we'll also talk about what can be done there.

Sometimes people call in, saying, "Well, I don't want to buy an Exchange server," or "Lotus Notes is our corporate standard, what can I do?" Or, "I need help being able to set up with a POP3 server."

Hopefully some of the things that Joseph covered earlier about going through the setup steps — it's pretty much the same thing as setting it up with an Exchange server, but there are a couple of things to keep in mind when you're using a POP3 server. We'll talk more about that.

Third-party mail clients. Again, we have somebody saying, "Well, Lotus Notes is our standard. We have to use that. What can we do about it?" Essentially, our answer is, right now, to treat it as a POP3/SMTP server. There are possibly some programming things that could be done, but we haven't really tested that, so that's really beyond the scope of this topic today.

Another question is, "Well, my SQL Server is behind the firewall or in the DMZ, and I can't use a domain account. What can I do then?" Well, if you can't use a domain account and you have to use a local account, then really, you're restricted to using a POP3/SMTP server to work with SQL Mail. You can't have an authenticated log in that a domain account allows you to have to be able to provide the credentials in your protocol, and the only way to do that is to use a POP3/SMTP server.

Turning to the next slide (slide 19), and continuing with problems, a bad profile. Sometimes we get error messages saying that the profile is not correct, or something like that. And usually the best way to fix that is to just delete the profile and re-create it. Or if you have a profile that works, make a copy of that and just give it a different name. The profile, if it can work with Outlook by itself, should work with SQL Mail. But if it doesn't work with Outlook, on its own, then it's never going to work with SQL Mail.

Another problem that we get is, "Well, I've got a profile and I tested it, and it works just fine, but when I try to start SQL Mail it doesn't work. What's going on?" The Test button just tests to see whether the profile is valid, that SQL Mail could be started or stopped with that profile. It doesn't guarantee that SQL Mail will use that profile. This is why Joseph stressed earlier the importance of looking to see what the default mail client is, because that is what SQL Mail is going to use when you try to send mail — it is going to use the default mail client, not necessarily the mail client that's associated with the profile that you've established with SQL Mail.

Lastly, Outlook is not the default mail client. That's more of a solution rather than an actual problem that people call in on. But just to kind of reiterate something I mentioned earlier, there are all sorts of things that can cause Outlook not to be the default mail client. So it's not like you have to have actually done something to change the default mail client, and it's very possible that installing Internet Explorer, Netscape, or some other application may change what your default mail client is; or just opening up Outlook Express and clicking on "Yes, I want this to be my default mail client" might cause that to happen.

Turning over to the next slide, slide 20, we will talk more about the problem that people call in on, where the mail is stuck in the Outbox, and recently we've had people saying that the mail was stuck in the Inbox. This is an old problem that has been around for quite a while. With previous versions of Windows Messaging, which shipped with NT 4.0, that was a very common issue, because just out-of-the-box, NT 4.0 had Windows Messaging, which was kind of a precursor to Exchange, before we really had Exchange, when we were still using Microsoft Mail and those sorts of things.

It's a very old client and it can be used with a POP3/SMTP server. And many customers like to use this, because it was something that was already on the box. They didn't have to go out and purchase anything. But the current problem was when you would actually send the mail using xp_sendmail, you would get the message saying that the mail was sent, but you didn't actually receive the mail. And if you opened up the Windows Messaging client, then you would see that message sitting there in the Outbox. And of course, in the process of opening up the client, one of the first things that it does is it sends out any messages that are waiting in the queue. So you would say, "Well, the message gets sent as soon as I open the client, and the only way that I can get it to work is to leave the client open at all times." That was a problem that was due to an incorrect version of the Mapi32.dll, and it was fixed in Service Pack 3. But that caused some other problems with some other applications, so it was not put in Service Pack 4, 5, or 6 of NT. So if you happen to catch that version, we had it available as kind of an as-needed basis, where you could call in and we would send it to you. But because Windows Messaging is old technology, now we don't even recommend that you use it. Outlook is better to use.

Now, one of the things that we've seen since Outlook 2002 came out is that this sort of seems to be the default behavior, that the mail is stuck in the Inbox when it's used with a POP3/SMTP server. And as Joseph pointed out, this is a consequence of the unified configuration in Outlook 2002. They brought in the MAPI spooler and made it in-process for Outlook for any non-Exchange type of servers. The way that it works — and this is really outside of our control, because we're using the mail functionality that's already there on the machine; it's really the way Outlook is configured to work — unless you have the client open, then the MAPI spooler, because it's run in-process, doesn't run, and so the mail is not actually sent out, it just kind of sits there in a queue and it waits until the client is open. And then when the client is open the mail is sent out. So it's not a bug, it's the way that the tool was designed to work. Unfortunately, it doesn't work very well for something like SQL Mail, which is trying to send mail. And you don't want to have the client open on your machine, and you don't necessarily even want to be logged in to the machine in the first place. So it's something that we're working on, but just to point out again, it's kind of out of the control of SQL Server, and we have to rely on the Outlook people to help us out here.

Turning to the next slide, number 21, there are a lot of MAPI-related problems. It's not something that people call in on and say, "I've got a bad Mapi.dll." It's something that we discover in the process of working with them. As I pointed out earlier, an incorrect Mapi.dll could cause the outgoing mail to be stuck in your Outbox and not be sent to the client that's open. It was fixed in SP3, but not later.

But the Mapi32.dll, which has all of these mail programming interface calls, continuously changes in size and functionality. Prior to Outlook 2000, it was on the order of 700 KB or 800 KB. When Outlook 2000 came out, there was a completely different design, and it went down to about 128 KB. And now you still have the Mapi32.dll, but it is kind of a redirector to some other .dll that they have, so that older applications, like SQL Mail, which are still using the MAPI calls, can point to the Mapi32.dll, and then they'll be redirected to where the programming interface items actually reside. So it'll redirect it there. There are two different versions of it with Outlook 2000. You have a version that's in the WinNT\System32 folder. That's where the older applications, like SQL Mail, would go to look for it. And then you have another version that's in C:\Program Files\Common Files\System\Mapi\1033. So that's where Outlook actually goes to find it.

I had a customer for whom Outlook was working just fine, but SQL Mail wasn't, and we found out what was happening was that she had one version of a Mapi32.dll in Program Files\Common Files\System\Mapi\1033, and then she had a completely different version, an incompatible version, in the WinNT\System32 folder. And the solution was to just copy what was working in the Program Files\Common Files\System\Mapi\1033 to the WinNT\System32, and then SQL Mail was able to work just fine. So we don't know what happened, what caused that mismatch to appear, but that sometimes happens, and that's one of the things that we'll check. If going through the normal troubleshooting steps doesn't turn up anything, then we'll start looking to see, what's the size? What's the date of your Mapi32.dll? Because the version numbers change, so you can't really look at the version numbers. Where are they located? Maybe we'll find a mismatch, or something like that.

Then finally, the last thing about MAPI is that some applications will replace a working version of Mapi32.dll. They'll rename it to Mapi32x.dll and copy some type of MAPI stub or something in its place. I know that Internet Explorer, I forget which version of it, did this. I think when you make Outlook Express your default e-mail client, it will do this. And there could be other things that will do it that I'm not aware of. So one of the things that you can do, if all else fails, if you have something that used to work, and now it doesn't work, is maybe you could look for a Mapi32x.dll and then rename Mapi32x to Mapi32, and then stop and restart SQL Server, and then see if SQL Mail will work.

Moving on to the next slide, slide 22, some of the problems with POP servers. Every mail server, as far as I know, supports SMTP, and most can support or can be configured to support POP3. Usually the problem is that, for some security reasons or whatever, people do not want to configure their Lotus Notes or GroupWise server to support POP3.

You have to have a POP3 server to use a client like Outlook. Because one of the things that it does is, when it starts up, it first checks the POP3 server to see if there is mail, and then it will send out any mail to the SMTP server. So you have to have a POP3 server. Well, some people might say, "We're never going to send anything to SQL Server, so there will never be anything incoming." Unfortunately, the way that the Outlook client works is that it does check the POP3 first, so you have to have a POP3 server even if there is nothing that will ever be on it. And so some people will obtain a shareware or freeware POP3 server and install it, knowing that there will never be anything on it, just so that they have a server that Outlook can go to.

POP3 servers, if you don't know what one is, you've probably used it yourself if you have mail at home. When you go home and you check your e-mail to see if you have e-mail, probably 9 times out of 10 it works just fine, but then every so often when it tries to connect, you wait about a minute, and then you get this message popping up saying that you timed out, do you wish to retry or continue? And this is why Joseph was stressing that you have to have 100 percent connectivity — if something like this happens in the SQL Mail world, then it will cause SQL Mail to hang. That message appears on a virtual desktop because SQL Server runs as a service, but we don't see the virtual desktop, so you'll never be able to take any type of interaction, so it will cause SQL Mail to hang.

SQL Mail could also hang with Exchange, if the Exchange server is not running. Exchange is just a little bit more robust in that if it can't send it right now, it will retry a little bit later on. The SMTP protocol doesn't do anything like that.

The next slide, number 23. We were talking about SQLAgentMail, when Joseph was going through the whole process. You can configure SQLAgentMail to notify you after a job is completed. To specify that there is a distinction between SQLAgentMail — SQLAgentMail is more like an alert, which you want to fire automatically when some job is completed or some other situation, like you're running out of memory on your server. SQLAgentMail will fire in a situation like that, in an alert situation. That's an asynchronous type of operation. It will fire or at least it will send it, and then it will move on and do whatever else it needs to do. And behind the scenes it will assume that the mail will be sent. It doesn't wait for any type of acknowledgement. However, if you have some scheduled job, and inside the scheduled job you are, for example, making an xp_sendmail call, even though that scheduled job is run by SQL Agent, you're not using SQL Agent in that situation. If you're using xp_sendmail, you're using SQL Mail. So if your mail hangs because of some of these situations that we've talked about, a POP3 sort of situation, then SQL Mail will hang. It's a synchronous operation at that point; if you're using the xp_sendmail, it will wait until that mail sent comes back, and until it gets it, it will just sit there and wait. If that happens, you could have a situation where no other scheduled jobs will run.

I mentioned that some customers will obtain some type of a POP3 server so that they have a server, even though they know that no mail will ever be incoming. Some people will also install a POP3 server on the SQL Server box itself to ensure that they have the connectivity that they need to have for it to work. Another thing that it allows you to do is that now that you have your mail server under your control, it's no longer a question of, "Well, they took it down Saturday for maintenance, and our SQL Mail started hanging." If the server is on the SQL Server box itself, then it's under your control. If it's down, the SQL Server is probably down as well.

Turning to the next slide, slide 24, we're talking about SQL Mail hangs. We've already gone over this, where the connection cannot be established and it hangs. And any dialog box that appears can cause that as well. One of the dialog boxes that I mentioned earlier was the client security screen. Turning to the next slide, slide 25, here are some of the security features that cause a security dialog box to appear.

As I mentioned earlier, it is very tricky to try to troubleshoot this, because when you send mail through Outlook itself, it works just fine. If you think that this could be the problem, what's causing the SQL Mail to hang — it works fine from Outlook, but it doesn't work from SQL Mail, and you went through the configuration steps and everything looks okay — one of the things that you can do to test this is to start up SQL Server from a command prompt. And once again, this is on slide 25. You would open up a command prompt window. You would go to the Run box and then type in command or create a command prompt window, and when you get the command prompt window, you would go to where SQL Server is installed, and then you would type sqlservr –c. What the –c does is cause it to start up SQL Server as an application, and not as a service.

You will see things scrolling across the screen, and that's the normal kind of information that goes to the error log. There will be a point where things will stop scrolling across the screen. You will not get the command prompt back. Leave that open, because that means that's where SQL Server is running. And then after you've done that, you can open up a Query Analyzer and make a connection to it. If you're in the Enterprise Manager, that's fine. You will see a red light indicating that SQL Server is not running — but it is running. You know it's running, because you have it on the command prompt, but the thing is it's not running as a service. It's running as an application, so that's why it doesn't see the service running. Make a connection to the server with Query Analyzer, and then try xp_sendmail. Try sending xp_sendmail, and then sending it to yourself or to somebody else, and then a sample message. And then, after you execute that query, if you then see a pop-up message appearing, saying something like another application is trying to send mail on your behalf, then you know that it's the security patch that's causing the problem.

Joseph: In other words, this is a troubleshooting step that we often use if we're not sure why the hang situation occurs. This is a good troubleshooting step for you to explore. If you do see a hang situation, you may want to start the service as an application, and check if you do get messages popping up.

Dwayne: Right. The security step, I'm not an Exchange expert, so it could be possible that you could go in and configure your local setup so that certain types of messages won't invoke this pop-up screen. But that's something, really, that we would have to encourage you to pursue with Exchange support, if you need help configuring that, so that the security dialog box does not appear.

Turning to slide 26, third-party mail clients. Joseph pointed out the various examples, like Lotus Notes or Novel GroupWise, and there is no guarantee that these will work with SQL Mail. We haven't tested them ourselves. I've done a little bit of limited testing with Lotus Notes. I installed a Lotus Notes client, and I got it to work with a Lotus Notes server that we have in the lab. I also installed Outlook mail, and there is a service in Outlook mail, at least there was at one time, where you could configure Outlook support as a Lotus Notes client. And I was able to get that working. I was able to open up Outlook, and Outlook would try to go to the Lotus Notes server. But what I noticed was that it would prompt me for a password every time I opened up Outlook, and that just will not work with SQL Mail. Because if that password pop-up appears, then every time you try to start up SQL Mail, it's going to happen on your virtual desktop. You'll never see it, and it will never work. So because of that, we say that we don't support any third-party mail clients, or even support Outlook configured with a mail service other than the Exchange Mail service, or other than the Internet Mail service.

Joseph didn't go into a lot of detail on this, but when you configure your mail client, if you don't have a profile already, then there's going to be a wizard that's going to pop up and walk you through the steps of configuring the mail client. It's going to ask you "Are you connecting to an Exchange server, or are you connecting to an Internet Mail server?" It will, through the wizard, configure the mail services. If you do it yourself, manually, then you would have to be performing actions like clicking Add service, and then you would see the list of services that are available. Then you would select the services that you want, such as Exchange, Internet Mail, Outlook Address Book, and whatever other services you want. You would add those to your profile. It will also give you, at that point, the option of specifying what name you want to give to your profile. If you're using the wizard, it gives you a name automatically; it doesn't ask you for one.

Turning to slide 27, some of the known issues with Outlook that we're aware of. Outlook ships with Office 2000, it also ships with Office XP, and it's available as a standalone product. It works with Exchange and it works with POP3.

Outlook 2000 and previous versions had an Internet Mail Only option that will not work with SQL Mail, and we talked about this previously. You have to install all of Outlook 2000 with the Corporate or Workgroup option. And there is a button in there that you click, under Tools, Options, Mail Services, and then it's Reconfigure Mail Support. You click that button and it walks you through the wizard. You can see right there whether it's configured as Corporate or Workgroup or Internet Mail Only mode. You don't have that with the Outlook 2002 version that ships with Office XP.

There is a security patch for Outlook 2000 that will cause SQL Mail 7.0 to hang. That's because of what we were saying earlier, that SQL 7.0 makes a simple MAPI connection to the mail client, and any simple MAPI connection will cause that dialog box to appear. SQL Mail with SQL Server 2000 uses an Extended MAPI connection. So if you're trying to use that with Outlook 2000, that will work, because you're making an Extended MAPI connection, and the Extended MAPI connection will not cause that dialog box to appear.

The problem with Outlook 2002, which ships with Office XP, is that it will only work with an Exchange server, and as we pointed out before, that's because of the way that they've combined the IMO and the Corporate and Workgroup options together, so that you have to have the client open. It will work, as long as you're willing to have the Outlook 2002 client open at all times on your machine. But that would require you to keep that minimized and to not log out of the server. You'd have to be logged in at all times, and either lock the station down or have some type of password protected screensaver, or something like that. And most people are just not willing to do that.

The next slide, slide 28, covers the known issues with Outlook Express. I go through this because a lot of people do try to configure SQL Mail with Outlook Express. Outlook Express comes with Internet Explorer 4.0 and 5.0, but it will not work with SQL Mail. It works with POP3 servers, but it's that lightweight type of mail functionality. It doesn't have the full MAPI functionality that SQL Mail needs.

Some of the other things that we've seen are that it doesn't create a mail profile, so there's no mail profile that you can use to assign in a SQL Mail configuration. Some of the things that I've seen are, the Internet Explorer 5.0 installation makes Outlook Express the default mail client, whether you want it to or not, and that could conflict with your SQL Mail startup. When it installs, it installs a stub of your Mapi32.dll, and that could replace an otherwise working version. We've seen that before, where somebody said, "Well, SQL Mail was working just fine, and then I installed Internet Explorer, and now it's not working," That could be the reason.

So that concludes the formal part of the presentation. We'll turn it over to Jason for questions.

Jason Bennet: Okay. Thanks so much for that presentation, Joseph and Dwayne.

Just a couple of quick notes before we move on to the Q&A portion of the Support WebCast. To access information on all upcoming Support WebCasts, and the archived content from past WebCasts, an easy-to-remember URL is http://support.microsoft.com/webcasts/.

The Q&A portion of the Support WebCast is intended to encourage further discussion of the Support WebCast topic. One-on-one product support issues are outside the scope of what we're able to address here today. So if you do need technical assistance, contact Microsoft Product Support Services and speak to a Support Professional. Or you can go to http://support.microsoft.com/ and you can submit an incident right there on the Web.

I also want to thank the users who have been writing in with this feedback on SQL Mail, and issues that they've seen running SQL Mail. I am going to pass that feedback on to the presenters after the broadcast.

Okay. So the first question we have: I successfully got SQL Mail to work on a cluster, even after fail over, after upgrading Office to Service Pack 2. Would you expect any future problems?

Joseph: As I mentioned earlier, the SQL Mail configuration on a cluster environment is something that we have seen a lot of people able to configure successfully, and we do our best to help them whenever we can, to configure that and get it to work. We're only hoping that maybe MAPI will be made cluster aware, so that it can fail back and forth between cluster nodes. But at this point, it is not. So in the future, if this functionality is provided, we are all going to welcome it, and it's going to be great for SQL Server, primarily for our customers who use SQL Server in a cluster environment, which is a scenario that we see more and more frequently.

So as far as future problems, I cannot say what could be a future problem. But we're hoping that sometime in the future MAPI will be geared more toward cluster types of environments.

Dwayne: Would it be safe to say that, as long as we don't have a failover situation, if you have it working, it should continue working?

Joseph: Absolutely. We've seen, in most cases, that as long as the server application does not fail over to the other node, everything works fine. And actually, a lot of times people are able to configure the mail in such a way that even if it fails over, it picks up the profile, and all of the information on the secondary node and the mail continues to work successfully.

We have seen a lot of people have success with this, and as I said, we're going to continue to provide our best effort support to ensure people have success with SQL Mail in a cluster environment.

Jason: Can you set up SQL Mail with SMTP only, without installing Outlook?

Dwayne: Not in a supported way. I think some people have been using CDONTS, and that's a programmatic way of making calls. I'm not a programming expert by any stretch, so I don't know what's really involved with that, but I guess it's theoretically possible to configure it without having Outlook installed. And that's something that I definitely plan to do more research on.

Joseph, do you have anything to add?

Joseph: Yes. Theoretically you can write your own client that's MAPI compliant, and that uses the MAPI interface. And you can certainly avoid installing Outlook by doing that.

Another example, also, is that with the Collaborative Data Objects (CDO) model that Dwayne just mentioned — we have seen isolated cases where people do turn to that model, depending on how much development effort they want to spend on this.

Another thing to cover here is that we focus on Outlook, yes. For example, with SQL 7.0, you don't have to have Outlook itself. You can have the Windows Messaging client; it is pretty old now, but it's still available with Windows NT. And it works just fine, as long as you have the correct MAPI .dll that does not cause the hang situation in SQL Mail.

So those are the kinds of cases where you can avoid using Outlook, per se. Outlook is not really the only client, but —

Dwayne: There's the Exchange client, too. The Exchange client was a precursor to Outlook, and it was available with Exchange 5.0. I don't know if Exchange 2000 has the Exchange client. It might still be downloadable somewhere. I really don't know about that, but that's something else that probably works.

I don't know about SQL Mail 2000. I think that would work with the Exchange client, but I haven't tested that, and I'm leery about recommending anything that I haven't tested myself.

Joseph: Right. To follow up on that, it probably won't be able to work, because of Extended MAPI functionality.

Dwayne: That's what I was wondering about.

Joseph: Okay.

Dwayne: The answer is yes, that you probably can get SQL Mail to work without having to install Outlook. There's nothing that I can recommend as something that we would support. You're kind of, I guess, on your own. We'll try to help you out if we can, if you're trying to configure something, but just be aware that the only thing that we really support right now is the Outlook client.

Joseph: Outlook and Windows Messaging for SQL Server 7.0

Dwayne: Right, but Windows Messaging only with Windows NT 4.0. Windows 2000 doesn't have Windows Messaging.

Joseph: In other words, there are options out there.

Jason: Okay. The next question: What is the security impact of sending query results via SQL Mail?

Joseph: That's an excellent question, and it really depends on how secure your mail environment is. I'm not an Exchange expert or a mail server expert, and I'm not sure how much of one Dwayne is, but it really depends on how secure your mail is. If you have an environment where you're using encrypted mails, that may be one way to increase the security of your mail or mail delivery, or to protect your mail. But the security is as high as that of sending any other e-mail. So as long as you're comfortable with the level of security that you're using between your mail client and mail server, then you can use it to send queries back and forth. But if you're not comfortable with that level of security, then you'll probably want to take some steps toward either increasing that level of security or, if that's not possible, avoid sending the queries with confidential data, or whatever queries that you're using.

Jason: Can we customize messages sent from SQL Agent? For example, I only need the subject, excluding the message body, sent to my pager.

Joseph: [Follow-up answer: When you are sending SQL Agent messages from alerts, there is a way to disable including the alert text in the body of the message. In the Enterprise Manager, go to Alerts and double-click on the alert you need to change. Go to the Response tab and look at the following option: Include alert error text in: E-mail, Pager, Net Send. You can clear any of the check boxes there.

However, the alert message is not included. When I tried this with an e-mail message, I still get the headers of other fields that are included in the body by default. Like this:

Jason: This user sent a couple of questions, and I thought they were worthy of note, so I'm going to ask them together. He's referring to running Windows 2000, SQL 2000, Outlook 2000 set up as IMO. I don't have any mail profiles in the Control Panel for Mail. How am I supposed to set up SQL Mail? When I try to type in my mail account in SQL, I get an error.

And then he adds (I think you answered his question): You can ignore my earlier question. It was due to the fact that I was running Outlook as IMO. I changed it to C/W and it worked okay. I'm a little confused though, because your earlier slide said we could use IMO. Can you comment on that a bit?

Dwayne: Which slide is he talking about? I thought we were pretty clear that it will not work with IMO.

Jason: Okay. If that user wants to follow up and let us know what specific slide they were talking about, we can certainly take that during the WebCast.

The next question: When configuring multiple SQL 2000 instances on the same machine or on separate machines, what are the recommendations, dos or don'ts, for using the same account for SQL startup or mail? Should we configure all SQL Server accounts to be the same domain wide, or should there be separate SQL accounts, log ins, and mail for every instance of SQL Server 2000?

Dwayne: That depends what you want to do. I don't see any problem with using the same service account to start up all of your SQL Server instances. If you want to have mail appear to come from one profile or another profile, then you could configure two instances of SQL Server 2000, one with a particular startup account and a particular profile, and another with a completely different startup account and a completely different profile. And mail sent from one will appear to come from one person, and mail sent from the other will appear to come from a different person. So there shouldn't be a problem with that.

Joseph: In other words, there isn't a recommendation, as far as performance reasons, or for any other reasons, why we would say go with the one configuration versus the other. It's really up to your business requirements, how you want to receive the e-mail. Both configurations that Dwayne listed as examples are possible, and are completely configurable. And you can pretty much configure it however you would like to.

Dwayne: What I would recommend that you avoid doing is having multiple mail profiles and using them with the same account. In other words, the example that I gave had one instance with one startup account with one mail profile, and another instance with another startup account and another mail profile — that's the way to go. If you try to have one instance with one startup account and multiple mail profiles, and then switch back and forth, you're probably going to start to see problems. We're working on some issues right now with that. So I think the best advice to give you now is just to avoid doing that.

Jason: Is there any way to set up mail alerts for clustered servers in Windows 2000 and SQL Server 2000? I can't get the servers to list the profiles.

Dwayne: So it sounded like the problem is with the profiles not appearing when he tries to select them from the drop-down box.

Joseph: Right. I think we covered that in a couple of places in the presentation. You will probably want to go over all of the steps that were listed and make sure that the profile gets listed in the drop-down box.

Dwayne: Right. It could be that the service account is not the same account as what the mail profiles were configured to use. There could be, maybe, some repermission issues on the registry keys, or something like that. You could try typing in the profile name, but I'm not going to guarantee that that's going to work.

Joseph: Yes. And again, one thing that we kept stressing was you want to make sure that you log on with the startup account for {SQL Agent} to configure the profile. That's the most common reason for —

Dwayne: That would be the startup account for SQL Agent in this case, because triggering an alert, maybe that's the problem. Maybe you have two different accounts, one startup account for SQL Server and a completely different account, the startup account for SQL Agent, or maybe SQL Agent is set up for local system, or something like that.

Joseph: Yes. In this particular case, exactly. Thank you. For SQL Agent, you want to log in with the SQL Agent account and then configure the profile for SQL Agent so that it shows up under the Enterprise Manager where you want to configure it.

Jason: If SQL Mail hangs, does Microsoft SQL Server hang? Also, how can I tell if SQL Mail is hung?

Dwayne: If SQL Mail hangs, it should not hang SQL Server. Anybody connected to the server should continue to be able to run their queries and things like that. You would notice that it hangs, specifically, if you open up Query Analyzer and use xp_sendmail to send a test message, and that mail isn't sent, you don't get back "this command completed," you don't get back anything. That's a good likelihood that SQL Mail is hanging. And if that happens, some of the things that we've done in the past is look for the MAPI spooler. There is a Mapisp32.exe, and if that's running, then you can try going in to Task Manager and stopping Mapisp32.exe. And then try to see if you can send SQL Mail. If that doesn't work, then what you'll probably need to do is stop and restart SQL Server to get it working again.

And just one thing that we didn't really mention in the presentation is that in SQL Server 2000 SQL Mail, there is no Start button. In SQL Server 7.0, you have to right-click on it, select Start, and get the little green light to start it. In SQL Server 2000, there is no Start option. Whenever you send the first piece of mail, if SQL Mail is not running, it will then start it up at that time.

Joseph: It will call the xp_startmail extended stored procedure to do that.

Dwayne: Yes. You can still use xp_startmail in SQL Server 2000; it's just not necessary.

Joseph: Right.

Dwayne: In SQL Server 7.0, unless you've started it up from Enterprise Manager or you've run xp_startmail, when you try to send your first mail message, it will say SQL Mail is not running.

Joseph: Right.

Jason: The next question, and I'm not certain I'm asking it correctly, so if the user wants to clarify, he can. Can I also use Outlook 97 or Outlook 98 for SQL Mail?

Dwayne: The first question we would ask is what version of SQL Mail? Are we talking SQL Mail 7.0 or SQL Mail 2000? SQL Mail 7.0 will work with Outlook 98. SQL Mail 2000 will work with Outlook 98 only if you have Service Pack 1 installed.

I've never gotten Outlook 97 to work, myself. The mail gets stuck in the Outbox. I have had a customer who was able to get it working successfully. In his case, applying the service pack for SQL Server 2000 allowed him to work with Outlook 97. But I don't guarantee that's going to work.

So I would say Outlook 97, I'm not so sure. Outlook 98, yes, it should work with SQL Server 7.0, and it should work with SQL Server 2000 if you have Service Pack 1 applied. There is a KB article, Q263556, "How To Configure SQL Mail." (http://support.microsoft.com/support/misc/kblookup.asp?id=Q263556). It lists the different mail clients and what works with what. I'm currently in the process of updating that to incorporate the latest information that we've found about Outlook 2002 or Outlook XP. So in the next week or two, you can look for an updated article coming out where, once again, we go through what works with which versions of SQL Server — which mail products work with which versions of SQL Server, as far as we know.

Jason: Is the mail profile case-sensitive when inputting it in the EM, just as it was when created in the Control Panel?

Joseph: No. Actually, it's not case-sensitive, and as far as I remember, the default mail client is MS_Exchange_Settings. So the caps or lowercase does not matter in the profile.

Dwayne: And that's the default when you have it set up with Exchange. If you have it set up for Internet, it will probably be something completely different like, Outlook_Default_Internet_Mail_Settings, which might get you beyond the 32-character limit. And when you look in your profile, 7.0 might show only the first 32 characters, and you might have a mismatch there, where the profile to use with SQL Mail is one thing, 32 characters long, and your mail profile itself is longer than 32 characters. In which case, if you tried to use the Test button, is not going to find that mail profile. So a simple solution to that would be just right-click on your Outlook icon on the desktop and select Properties, and then go into Show Profiles, and then just change the name of your mail profile.

Jason: What's the correct date and size of Mapi.dll or Mapi32.dll?

Dwayne: It depends on which version of Outlook you're looking at. It's very hard to say. If you call in for support and we ask you for the date and size, that's just for comparison purposes so that we can see if you get the same versions and the same size. We don't look for a particular size and date.

Joseph: And there are so many versions of the MAPI .dll that get installed by multiple applications out there, not only Outlook, but Outlook Express or some other mail client, or even your Internet Explorer version. So there isn't really one specific one. Exchange also installs a Mapi32.dll, so there are multiple applications that will install the Mapi.dll. And again, there isn't a right one versus a wrong one, but definitely, a lot of those versions work correctly. You want to look out for mail stubs that Dwayne mentioned earlier. Those are files that are not the actual Mapi32.dll, but they point to something else, or they're just there to fake the existence of one, a common scenario that Outlook Express presents us with. A great place to look for specific versions is again, the article on how to configure SQL Mail. It discusses what Mapi32.dll versions are installed by the different mail clients that we support, so it's really a great reference point.

Dwayne: Yes. I think for Outlook 2000 and Outlook 2002, the Mapi32.dll that gets installed with them is about 128 KB, and I don't know what the dates will be on that.

Jason: We're stuck in an environment where we're not in control of our Lotus Notes server. Is there a FAQ or KB article that would outline what I need to tell our mail admin?

Dwayne: I don't know what you mean by not in control of it. I guess if they take it down for service on a regular basis —

Joseph: The way I understand this is probably they cannot change from a Lotus Notes server to —

Dwayne: Yes. They're pretty much stuck with Lotus Notes. I think they're just not part of the decision-making process.

Joseph: Right. Again, the article Q263556, "How To Configure SQL Mail" (http://support.microsoft.com/support/misc/kblookup.asp?id=Q263556) mentions that, if they want to point it out to the mail administrator. I think it clearly states that any third-party servers running in native format will not work successfully with SQL Mail. Anything that's a POP3/SMTP server will. And that's an official Microsoft Support document, so they can take it to their administrator.

Dwayne: The Exchange people that I've worked with in the past have told me that all mail servers there support SMTP, so it shouldn't be a problem of being able to send mail to your Lotus Notes server with SMTP. The problem is whether the Lotus Notes server can be configured to be used with a POP3 server. And that's a problem I've heard at least two or three different customers tell me, that because of security considerations, they cannot have their Lotus Notes server reconfigured to be used as POP3.

Just to give you an example, in Exchange Server, you can use it as an Exchange server, but you can also use it as a POP3/SMTP server. I don't know that there is any configuration that would have to be done with it, but it would work as both at the same time, so it's not like you'd have to give up everything to convert it. I don't know if Lotus Notes is the same. I think it is. I think that you can configure it to be used as a POP3 server, allow people to call in with the POP3 protocol, and check their mail without giving up the functionality of the native Lotus Notes server. But because of security concerns, and if Lotus Notes administrators don't want people to be able to connect and download things in an insecure type of fashion, they don't have the Lotus Notes server configured to be able to be used as POP3. If that's the situation, and it's beyond your control, as the questioner seems to state, you're not out of luck, because it's possible that you could install a POP3 server on your SQL Server box itself.

Now, you'd have to be aware that you'll never have any incoming mail on that, or somehow configure it so that you could if that's the need. But if you know that you'll never have any incoming mail, you could get a shareware version somewhere out there, or a freeware version of POP3, and maybe there are even some other things that have that type of functionality, I don't know. But if you can get that installed and working on your SQL Server, then you can point to that POP3 server for your incoming server, and you can point to your Lotus Notes server as your outgoing SMTP server. They don't have to be the same. You could have two different server boxes. And maybe that would allow you to be able to still use Lotus Notes to send mail, and that would be sent throughout your environment. I've known customers who have been able to get this type of setup working.

Jason: Great. The Save sent mail option is disabled from our SQL Agent screen, but the Sent Mail folder is still getting full. Are there any known issues with this option?

Dwayne: I'm not aware of all of the issues. I don't believe SQL Server 2000 keeps a copy of sent mail. So it sounds like that's one of the SQL Server 7.0 options. You might be able to turn it off from the client side. Again, I'm not absolutely confident of that.

Joseph: [Follow-up answer: The option Save copies of the sent messages in the "Sent Items" folder in the SQL Agent Mail properties allows you to save messages in the Sent Items folder, and my tests on SQL Server 7.0 SP3 and SQL Server 2000 show that this option works as expected. I did not find any documented known issues with this option.]

Jason: The next question: How do we know if a mail client is MAPI compliant or not?

Dwayne: Documentation.

Joseph: That's pretty much documentation, yes. When you purchase any product, is has basic specifications, just like when you're purchasing your operating system, or your hardware, for that matter. So yes, the documentation that comes with the software would be a good source to give you that answer.

Dwayne: And then try it out and see if it works.

Joseph: Maybe the Web site of the vendor that sells that software can also provide that information.

Jason: Okay. What's the version number of the security patch mentioned in the presentation that would cause SQL Mail to hang in SQL Server 7.0?

Joseph: [Follow-up answer: This is regarding Outlook 2000 security patch. Attached is an KB article that gives details on the security patch:

Here is another link to the security patch itself:

Jason: Okay. Can you repeat the command mentioned to start SQL Mail from the command prompt?

Dwayne: It's not to start SQL Mail from the command prompt. It's to start SQL Server from the command prompt, and that's on slide 25. It's sqlservr –c.

Now, with SQL Server 7.0, you don't have to be in any particular directory, but with SQL Server 2000, you have to go to the \Binn directory where the SQL Server executables are stored. And by default, that's in \Program Files\Microsoft SQL Server\MSSQLServer\Binn.

Joseph: \Program Files\Microsoft SQL Server\MSSQLServer\Binn if it's the default instance, and \Program Files\Microsoft SQL Server\MSSQL$<instance name>\Binn if it's a named instance.

Dwayne: You have to be in that directory to run sqlservr –c.

Joseph: And another thing to point out, if you do have a named instance, you do need to use not only the –c option, but also to use the –s option, and that is the option used to specify the instance name. So if you're going to be using a named instance in SQL 2000, you would have to start your server with sqlservr –c –s and the instance name.

Dwayne: And here's a little tip. That's one of the first things that we do when people call in, saying, "I have a problem starting my SQL Server." One of the first things that we'll do is have them start it from a command prompt, just to see if that works. So that's something you might want to try out.

Joseph: But to answer the question of how to start SQL Mail from a command prompt, the closest thing that can answer this question is you can start SQL Mail from Query Analyzer, for example, or any SQL Server client by using the xp_startmail. That's the extended stored procedure that gets invoked by the SQL Server Enterprise Manager to start the mail.

Jason: Okay. Should you use two separate accounts, each with a profile for SQL Service and SQL Agent, or can you use the same account with one profile for both services?

Joseph: You can use the same account, or you can use separate accounts. Again, it's really up to your need. If you need to use separate accounts, you're absolutely welcome to do so.

Jason: Okay. Is there a problem logging into the system account after SQL has started? A Knowledge Base article stated that it could hang the mail.

Dwayne: I think that maybe they are asking, "Are there problems reauthenticating?" That's more a problem, if I understand the question, of when you're trying to send mail. There is a problem reauthenticating through the POP3 server. That could cause that pop-up message to appear, asking people to log in or enter their password, or something like that.

There shouldn't be a problem. If SQL Server is running, you can log out of whatever account that you are in, and it's still running as a service. It's not going to go away. And then you can log in again with the startup account that you're using to start up SQL Server. So as Joseph said earlier, if SQL Admin is the name of the log in account that you use to start up SQL Server, after it's running, you can log out of your own account, and then you can log back in with SQL Admin, and whatever the password for SQL Admin is, and that shouldn't be a problem.

Joseph: Right. Your services will continue running, if that's what the fear is, in this particular case — that they will stop.

Dwayne: One thing that just occurred to me is, a lot of times when you do configure things with SQL Mail, you might need to stop and restart SQL Server. For example, some of the things that we talked about: "Well, I changed my default client from Outlook Express to Microsoft Outlook, but SQL Mail still failed." Well, have you stopped and restarted SQL Server since then? Because when you start SQL Server, it kind of reads the registry files and it gets that information, and that's what it uses. And then if you change those, whether it's changing your default mail client or your default profile, or add a profile or these sorts of things, that information is kind of static. It was read in when SQL Server started. It doesn't see any changes that you've made to it. So whenever you're playing around with this and trying to reconfigure SQL Mail, one of the things that you probably want to do is, after you've made the changes, stop and restart SQL Server so that it can go out and get that fresh information. And then try to start SQL Mail and send mail again to see if that works.

I hope that answers the question.

Jason: Okay. It looks like that does answer all of the questions in our queue. If anyone has any feedback about today's presentation, or about presentations you've seen before, or if you want to give some feedback on the interface that we're using, you can send it through the e-mail alias feedback@microsoft.com. If you do that, please include "Support WebCast" in the subject line, and it will be routed to us.

I want to thank everybody for joining us. I hope the information was useful to you. We hope you join us again in the near future. Thanks, and good-bye.