tarclov winnie nakitah

[Trinidad Baltzell]

Another difference between the two is that email message sent using sp_send_dbmail() will be rolled back (not sent) if the transaction is rolled back. This does not happen with email sent using xp_sendmail().

I was sending emails to notify users if an SP was unable to complete it's processing. Of course, I was rolling back the transaction in that event. When I switched to sp_send_dbmail() the transactions that were being rolled back (the very ones I wanted to get an email notification from) stopped sending emails.

SQL Mail is a mail-enabled application. When you send mail, SQL Mail uses the Mapi32.dll file to make an API call to start the MAPI spooler of the Windows Messaging subsystem (Mapisp32.exe), send the mail, and then shut the spooler down. In older mail clients, the MAPI spooler was not designed to be used with an NT service. With these older MAPI spoolers, outgoing mail generated by SQL Mail remains stuck in the Outbox until the mail client is opened.

If mapi_profile_name and mapi_profile_password are provided, SQL Server attempts to log on to the MAPI provider using that profile name and password. If mapi_profile_name and mapi_profile_password are not provided, SQL Server uses the profile name and password specified in the SQL Mail Configuration dialog box. If no profile name or password is explicitly provided, SQL Server attempts to log in to the MAPI provider using the default MAPI profile. Some MAPI providers may be configured to use Windows Authentication, in which case the MAPI password is ignored.

If you use xp_startmail to start your mail sessions, you can optionally supply your login name and password so that you do not have to type it at the command prompt. However, SQL Mail will not run on top of an existing MAPI session if one is running. This behavior differs from SQL Server version 7.0 and earlier.

If there is an existing mail session, xp_startmail does not start a new one. If mail is being used on the same computer on which SQL Server is also running, the mail client must be started either before xp_startmail is run, or before SQL Server is started if SQL Mail is configured to start automatically when SQL Server starts.

Keep in mind that xp_sendmail requires a client such as Outlook to be installed on the server which was ALWAYS a bad idea, MAPI in this context has been causing problems for the entire time I've been dealing with SQL Mail. I have found SQL Mail to be considerably less stable and when it gets screwed up (usually all on its own) the only way to get it working again is to reboot the entire server.. DB Mail on the other hand was released in SQL 2005 and uses only SMTP to send mail. It is VERY stable and can handle significantly higher volumes of email than SQL Mail. It also returns immediately as soon as the email has been accepted by the process.

On SQL 2005/2008 I just don't setup SQL Mail, there is no good reason to use it and I have yet to hear a good reason to introduce that instability into the server. If there is a packaged application that requires it then I would likely push against it because I view it as a sloppy practice..

I currently receive 40+ emails each day from SQL Server 2000 instances and if I receive less then that I need to determine which servers are experiencing problems. This process is manual and therefor I'm looking for an automated way to verify the status of email communications. Each month we experience email (MAPI) failures on several servers due to DC or Exchange Server reboots or other unknown issues. I don't want to convert everything to SMTP since at some point next year we will start upgrading to SQL Server 2005.

You can set a procedure/job to run xp_startmail. If it returns the message it's started, then you know things are ok. If not, I'd suggest you install xp_smtp to send you a note or a "net send" if you have a box available when it runs, to let you know.

I'll try that the next time xp_sendmail hangs on a server, but I'm thinking I tried that once before and received a message indicating it is already started. The problem isn't MAPI stopping, but rather hanging. In most cases restarting SQL Server Agent service does not resolve the problem. I need to restart the SQL Server service. Strange thing is we only experience the problem with SQL 2000 Standard Edition and not Enterprise Edition. At my previous employer we had nothing but Enterprise Edition and never had an issue.

Unfortunately your suggestion didn't work. We just rebooted our domain controllers and sure enough several of our SQL Servers experienced the MAPI email problem where xp_sendmail hangs. I ran xp_startmail and received the message indicating "SQL Mail session is already started" and as usual, when I stop the query the process is not killed. When I try to kill the process it gets stuck in a Rollback state. Restarting SQL Server Agent releases the process, however the next time I run xp_sendmail it hangs. The only solution is to restart the SQL Server service.

"The SQL Server Agent mail connectivity is handled by a single mail interface thread. All the SQL Server Agent job uses this mail interface thread for is e-mail communication. Each SQL Server Agent job waits without any timeout limit until the mail command of the job returns from the mail interface thread. If the mail interface thread stops responding because of some external reason, the dependent job keeps waiting infinitely."

"What happens is that the mail client attempts to connect to the mail server. If the connection fails, a message from the mail client appears that requests user action as to whether to continue or cancel. The message does not appear on the server because SQL Mail is run from the SQL Server service. Because a message does not appear, no user action can occur and SQL Mail waits indefinitely. Even if you re-establish the network connection, SQL Mail does not automatically reset and retry. You must stop and restart the SQL Server service before you can begin sending messages again.

Another possible reason for SQL Mail to stop responding is a new Outlook security feature that warns when a non-Outlook application is sending e-mail. SQL Mail for SQL Server 6.5 and SQL Server 7.0 are such non-Outlook applications. If a security dialog box window appears when SQL Mail is sent, this has the same effect of making SQL Mail stop responding as if the connection to the mail server were lost."

I decided to create a second job, which runs 10 minutes following the job that tests SQL Mail communications. The second job checks sysjobhistory to see if the first job completed. If it didn't, I raise an error which is written to the application event log. A rule was then created in Microsoft Operations Manager to detect the error and send an email to me, notifying me of the job failure. Another option in place of MOM was to create an Alert that fires when the specific error is detected. The error could send me an email to indicate a problem with MAPI.

Why do you need to use SQL Mail? We in our shop used to have SQL Mail but most completely get rid of it because of this kind of problems. When our boss asked me to find alternative ways I found a lot. (xp_smtp was a great one!)

We have over 50 SQL Servers so converting to SMTP will take some planning. I was hoping to do this later in the year, but have reconsidered since we will begin upgrading to SQL 2005 next year. Once that happens MAPI will no longer be an issue.

My company migrated from an in house Exchange 2003 email server to a cloud hosted Exchange Online (part of Office 365), and my SQL Server 2000 using xp_sendmail could not connect to it. This is a selection of stored procedures that replicates the functionality of xp_sendmail, especially the feature of including a query result grid in the email body.

My solution uses CDOSYS to send email without Outlook installed. This has been documented in many places elsewhere, but most of the solutions are missing features. Microsoft's own article doesn't include attachments, cc, bcc, or the ability to include query results.

The optional objects are all related to email address name resolution. The problem is that our in house Exchange server would resolve "John Doe" into "jd...@domain.com" using Active Directory, and I couldn't get that to work here.

My solution (admittedly clumsy) was to write code to transform the name into the address. I could have simply changed all the names into email addresses and not worried about it, but that would mean finding all the places where the values are passed from SQL Agent jobs, as well as the many hard-coded instances (I haven't changed all the ones my predecessors set up). To use my name resolution fix, you will need to have consistent email naming procedures, and work out the logic to go with that.

From the following KB article it list couple of store procedures which can be used to send email through SQLServer. One of the feature it has the "From/Sender" can be passed in a parameter where as in case xp_sendmail it can't be.

Hi, I am trying to configure business alert notification emails. (GP10 SP4, SQL2005, Windows Server 2003 32-bit). I have completed configuring Database Mail in SQL Server and tested email successfully by receiving the test email in my inbox. Later, I have configured one existing GP business alert and inserted the recipient email address (my email id) and clicked Test Mail. It said "The test message was sent successfuly". But I did not receive any email in my inbox. I had setup a schedule for the alert. Business Alert history report shows that email has been sent. But actually no emails received in the inbox. What am I missing? Please help.

I tried to create a user profile with outlook express. The profile was created and I can send an email from outlook express with the account that is running the sql service and SQL agent. However, I too get the message that the mail client cannot fulfill requests. The mail portion is also missing from Control Panel. Do we have to install a full version of outlook, or is there something I am missing?

c01484d022