Skip to main content
TechNet

Taking Advantage of SQL Mail

Published: September 25, 2002

Host MS_Stephen_D:
Welcome to today's TechNet Chat, Taking Advantage of SQL Mail chat. I will ask the Hosts to introduce themselves.

Host Guest_Gert_MS:
Hi I am Gert Drapers, former Product Unit Manager of the SQL Tools team, the team responsible for SQL Mail, and creator of XPSMTP.

Host Guest_dwayne_MS:
My name is Dwayne Lanclos and I am a SQL Server support professional. I have worked on many SQL Mail issues and contributed seVeral KB articles on the subject.

Host Guest_michael_MS:
Hello. My Name is Michael Raheem. I am a Program manager in SQL Server Tools team working on SQL Mail. SQL Mail was developed and maintained by SQL Server Tools team. I am here to answer your questions and gather your ideas in improving this feature.

Host Guest_Joseph_MS:
Joseph Pilov - SQL Server Support Professional. I have been at Microsoft for 4 years. Worked with SQL Server 6.5, SQL Server 7.0 and SQL Server 2000 and have dealt with quite a few of SQL Mail issues and SQL Server administration-related problems.

Host Guest_Kurt_PASS:
Hi! I'm Kurt Windisch, PASS director of program development. Thanks for joining today!

Host MS_Stephen_D:
And... I am Stephen Dybing, SQL Server Communities PM. Glad you all could make it today!

Host MS_Stephen_D:
The Input Room is where you can enter questions for our Hosts today. We will read them and select questions to answer. The questions and answers will be posted in the Reading Room.

Host MS_Stephen_D:
Let's get started! Fire away with your questions for our Hosts.

Host Guest_michael_MS:
Q:
Vera: Is there a way to attach the query results in an XML format?

Host Guest_michael_MS:
A:
Michael_MS: Not currently. But we are considering this feature in the next versions of the product.

Host Guest_dwayne_MS:
Q:
Vera: Is there a way to ping the exchange server without getting an hig severity error

Host Guest_dwayne_MS:
A:
Vera, are you looking for a wait to determine if you still have a connection to Exchange?

Host Guest_Joseph_MS:
Q:
localsports : I am a newbie, but what does SQL mail do?

Host Guest_Joseph_MS:
A:
SQL Mail provides a way to receive e-mail messages generated by Microsoft SQL Server. Messages can be triggered to provide you with the status of a job or a warning caused by an alert. SQL Mail can include a result set in a reply to e-mail messages.

Host Guest_Joseph_MS:
A:
SQL Mail allows SQL Server to send and receive e-mail by establishing a client connection with a mail server.

Host Guest_Joseph_MS:
A:
There is also the SQL Agent Mail. SQLAgentMail can use its own domain account and mail profile that is different from the one set up for SQL Mail. With SQL Server, you can configure SQLAgentMail e-mail messages to be sent when:

Host Guest_Joseph_MS:
A:
A SQL alert is triggered. A scheduled task, such as a database backup or replication event, succeeds or fails.

Host Guest_dwayne_MS:
Q:
If the Exchange server is down xp_sendmail return a sev 16 or higher, I would like to do a ping to test if the server is up before hitting send, assuming that ping would return an output parameter with the result of the ping

Host Guest_dwayne_MS:
A:
True, if the Exchange server went down while you had a SQL Mail connection, the next time you run xp_sendmail, you will get an error. However, subsequent attempts will go through. I don't know of a way to ping to see if Exchange server is up.

Host Guest_Joseph_MS:
Q:
Pat : Just started using SQL Mail to alert me when jobs scheduled thru SQL Agent fail. What other things can I use SQL Mail for? My developers tell me they have another way of emailing in an app so I am looking at things a DBA would benefit from.

Host Guest_Joseph_MS:
A:
Pat, you are probably referring to SQL Agent Mail regarding the Jobs and Alerts. You can use SQL Mail for programmability, i.e. you can use it in your T-SQL code - stored procedures, triggers, etc.

Host Guest_Joseph_MS:
A:
Pat, here is an programmability example : EXEC xp_sendmail @recipients = 'robertk', @query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES', @subject = 'SQL Server Report', @message = 'The contents of INFORMATION_SCHEMA.TABLES:', @attach_result

Host Guest_dwayne_MS:
Q:
Tammy : We follow SQL Mail setup steps exactly as listed and can send e-mails from Outlook, but no profiles show up under SQL Agent properties. What did we do wrong?

Host Guest_dwayne_MS:
A:
Tammy, it sounds like SQL Agent is being started with a Local System account. Check the account used to start SQL Agent.

Host Guest_Gert_MS:
Q:
Vera: Is there a list of SQL Mail error return codes, it only retuns a cryptic hex return code

Host Guest_Gert_MS:
A:
There is a list of MAPI error codes

Host Guest_Gert_MS:
A:
238119 - MAPI Error list http://support.microsoft.com/default.aspx?scid=kb;en-us;238119&sd=tech

Host Guest_michael_MS:
Q:
I can replace SQL Mail, external replacements, how can I make SQL Agent Mail be SMTP only?

Host Guest_michael_MS:
A:
In SQL Server 2000 Service Pack 3 you will be able to configure Agent Mail to send mail via SMTP in Outlook Express

Host Guest_dwayne_MS:
Q:
Vera: I would like the equivalent of xp_test_mapi_profile for a live connection

Host Guest_dwayne_MS:
A:
I understand this as a xproc that can be run to check if the connection to the mail server is still active (perhaps after Exchange was rebooted). That's a good suggestion we will have to consider for a future release of SQL Mail.

Host Guest_michael_MS:
Q:
Vera : Cool, when will SP3 be available?

Host Guest_michael_MS:
A:
You maybe able to pick up the Beta next month - but no formal date

Host Guest_michael_MS:
Q:
Vera: Will we also be able to use Outlook Express for SQL Mail?

Host Guest_michael_MS:
A:
No, Agent Mail only.

Host Guest_Joseph_MS:
Q:
pcast01: What are some of the stored procedures included with sql server 2K that use SQL Mail, that say developers would find useful?

Host Guest_Joseph_MS:
A:
sp_processmail, xp_sendmail, xp_readmail, xp_startmail, xp_stopmail, xp_deletemail, xp_findnextmsg, xp_readmail

Host Guest_dwayne_MS:
Q:
Vera : Do I need Outlook XP or 2000, we only have a license for Office 97

Host Guest_dwayne_MS:
A:
For SQL 2000, you need Outlook 2000 or Outlook XP. I think Outlook 98 will also work. Unfortunately, Outlook 97 is not supported.

Host Guest_Gert_MS:
Q:
AndyCLondon : Can I use Groupwise or Lotus Notes for both client and server instead of Outlook and Exchange?

Host Guest_Gert_MS:
A:
There are two options: one if there is a MAPI driver available that would work best, second option is to configure SQL Mail for use with SMTP

Host Guest_dwayne_MS:
Q:
bclark : Has there been any progress on the 'mystery' problems with the SP2 sqlmail dll that affects SMTP mail? Dwayne is familiar with the issue

Host Guest_dwayne_MS:
A:
By "mystery" problems, I think you are referring perhaps to access violations. There is a post-sp2 that addresses many of these problems. This is discussed in 320407.

Host Guest_Gert_MS:
Q:
AndyCLondon : Are there any issues with SMTP?

Host Guest_Gert_MS:
A:
You need to be careful configuring SQL Mail, since it is not all the obvious, see 263556 - INF: How to Configure SQL Mail http://support.microsoft.com/default.aspx?scid=kb;en-us;263556&sd=tech

Host Guest_Gert_MS:
Q:
AndyCLondon : We are having a bit of trouble with Groupwise and SMTP. The MAPI spooler locks up with Outlook 2000. Will this be better win Outlook XP

Host Guest_Gert_MS:
A:
We found that some of these drivers do not work under a service, only from a user process because they require a message pump. You will find in the link I posted you best use SMTP in these cases

Host Guest_dwayne_MS:
Q:
bclark : Thanks.. I hadn't seen the 'Q' article. I assume it will be resolved in SP3?

Host Guest_dwayne_MS:
A:
Yes, the issue addressed in 320407 is scheduled to be included in sp3.

Host Guest_Joseph_MS:
Q:
Tammy : I'm fairly sure it's a domain account, and I know we've had this same problem with domain accounts. Any other ideas? We've

Host Guest_Joseph_MS:
A:
See article INF: Common SQL Mail Problems -
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315886&sd=tech Q10: Why are no mail profiles listed in the drop-down box in SQL Mail properties? Q20: Why does the Test button tell me that it can start and stop

Host Guest_dwayne_MS:
Q:
pcast01 : I am not familiar with exchange but is there a way to setup an email account with a profile that is on another domain, and use that for the SQL Mail profile?

Host Guest_dwayne_MS:
A:
If the Exchange server is in a different domain from SQL Server, you will either need to have a trusted account between the two (so that SQL Mail can connect to the Exchange server) or you will need to treat Exchange as a POP3/SMTP server.

Host Guest_michael_MS:
Q:
Vera : Will the SP3 fix for SQL Agent also work with POP3 authentication?

Host Guest_michael_MS:
A:
POP3 is used for receiving mail. Agent Mail does not have a "receive" functionality

Host Guest_michael_MS:
Q:
Vera : Any change we will see support for multiple result sets as attachements or in the body text in SQL Mail

Host Guest_michael_MS:
A:
This is a great suggestion. We will look into it.

Host Guest_michael_MS:
Q:
Vera : Sorry I meant that my SMTP server requires me to use a user ID and password

Host Guest_michael_MS:
A:
No. It does not require authentication.

Host Guest_dwayne_MS:
Q:
AndyCLondon : Has anyone else experienced lockups when using Outlook 2000, SQLMail and SMTP?

Host Guest_dwayne_MS:
A:
SQL Mail hangs when using POP3/SMTP servers are very frequent. See question 12 in 315886, "Common SQL Mail Problems".

Host Guest_Joseph_MS:
Q:
terry : Hi is it possible to run SQL Server Agent mail on SQL 2000 without installing Outlook?

Host Guest_Joseph_MS:
A:
Terry, the short answer is NO. please see the following article INF: Frequently Asked Questions - SQL Server - SQL Mail -
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311231&sd=tech Q1: Why do I need to install a mail client on my SQL Server comput

Host Guest_michael_MS:
A:
Vera : My SMTP server DOES require me to provide a user ID and password, so how do I provide this information, is it stored inside the Outlook Express profiile? Outlook Express has this check box that says "Server requires login" or something like that

Host Guest_michael_MS:
A:
Yes. You need to provide this information in Outlook Express. (By the way your questions rock!)

Host Guest_Joseph_MS:
Q:
AndyG : We are a Microsoft shop with one glaring exception...we run Lotus Notes. Can SQL Mail work with Lotus Notes (apologies if this has already been asked)

Host Guest_Gert_MS:
Q:
Vera : What is the support policy for external solution like XPSMTP from http://sqldev.net/xp/xpsmtp.htm or others

Host Guest_Joseph_MS:
A:
I saw that answered already by AndyCLondon , but just for the record: INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311231&sd=tech Q5: Can I use SQL Mail with mail servers like Lotus Notes

Host Guest_Gert_MS:
A:
http://SQLDev.Net supports XPSMTP, this flaws outside of the responsibility of Microsoft PSS

Host Guest_Gert_MS:
A:
Sorry that should say falls outside of the scope of Microsoft PSS

Host Guest_Joseph_MS:
Q:
Vera : Isn't CDO Single Threaded Appartment

Host Guest_Joseph_MS:
A:
Yes, CDO is single-threaded apartment, see this article 239096 PRB: Problems with CoInitializeEx and CDO http://support.microsoft.com/default.aspx?scid=kb;EN-US;239096&sd=tech

Host Guest_dwayne_MS:
Q:
Vera : Is it save to use CDO from sp_OA? It seem to take up a lot of resources

Host Guest_dwayne_MS:
A:
sp_OA procedures do take up some resources. Before making calls to CDONTS or CDOSYS from within stored procedures using sp_OA on a production server, you should thoroughly test your application.

Host Guest_dwayne_MS:
A:
See 312839 HOW TO: Send E-Mail Without Using SQL Mail in SQL Server for examples of using CDOSYS and CDONTS in a SQL Server stored procedure.

Host Guest_Gert_MS:
Q:
Vera : So it would block a thread inside SQL Server

Host Guest_Gert_MS:
A:
Since CDO is not multi-thread safe and sp_OA is Automation (IDispatch) based, you can have problems calling CDO from more then 2 users at the time, since the access to the object will be synchronized. This is a general concern with regards to using sp_OA

Host MS_Stephen_D:
We're nearing the end of this chat. If you have any additional questions, please post them now. If we missed your question, please repost it. Thanks!

Host Guest_michael_MS:
Q:
Vera : What more changes for SP3 we can expect?

Host Guest_Joseph_MS:
Here are some commonly-rereferenced articles that can answer a very large majority of the SQL Mail questions: 263556 INF: How to Configure SQL Mail
http://support.microsoft.com/default.aspx?scid=kb;EN-US;263556&sd=tech 321183 HOW TO: Troubleshoot Your SQL M

Host Guest_michael_MS:
A:
Lots of goodies!!! You can read the "readme" when the beta becomes available. Ohter than SMTP support for agent mail, there is no new functionality for SQL Mail.

Host Guest_Joseph_MS:
321183 HOW TO: Troubleshoot Your SQL Mail Issue http://support.microsoft.com/default.aspx?scid=kb;EN-US;321183&sd=tech

Host Guest_Joseph_MS:
315886 INF: Common SQL Mail Problems http://support.microsoft.com/default.aspx?scid=kb;EN-US;315886&sd=tech

Host Guest_Joseph_MS:
311231 INF: FAQs - SQL Server - SQL Mail http://support.microsoft.com/default.aspx?scid=kb;EN-US;311231&sd=tech

Host Guest_Joseph_MS:
312839 HOW TO: Send E-Mail Without Using SQL Mail in SQL Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839&sd=tech

Host Guest_Gert_MS:
Q:
Vera : I notice that I do not need ti use start and stop mail in SQL 200, but I have to in 7.0, is it required in 2000

Host Guest_Gert_MS:
A:
In 2000 this is not required, since there is no longer a background thread for dispatching the mail, like in 7.0 which was required because of Simple MAPI limitations

Host MS_Stephen_D:
A:
Thanks for joining us today! You've asked some great questions, unfortunately, it's time to go.

Host Guest_Kurt_PASS:
Thanks for joining the chat today! A transcript will be available on www.sqlpass.org/ and the TechNet site shortly.

Host Guest_michael_MS:
It was a pleasure chatting with you. Please send your feedback/ideas to SQLWISH@microsoft.com.

Host Guest_Gert_MS:
Bye everybody thanks for attending

Host Guest_dwayne_MS:
Thanks for your participation

Host Guest_Joseph_MS:
Good-by. It was a pleasure. Thanks