November 23, 2009 at 11:06 pm
Hi all,
I was testing a xp_sendmail stored procedure. I did not configure any database mail on my machine.
There is no IIS configured on my machine as well.
But still am able to send an email.
Just want to know what is happening behind.
How it is able to send an email.
exec master.dbo.xp_sendmail
@recipients='testusr@gmail.com',
@subject = 'Test Mail',
@message = 'FYI'
Is that any other SMTP server coming into picture.
I can see the mail come from my cleint smtp server.
How come xp_sendmail internally getting communicated with that SMTP Server ????
Any valuable comments will be greatly appreciated.
Thanks in advance
November 24, 2009 at 12:26 am
what does
sp_configure 'database mail xps'
say?
Execute
EXECUTE msdb.dbo.sysmail_help_account_sp
to see accounts configured.
November 25, 2009 at 12:03 am
Hi Roshan,
Below is the ouput of the two commands
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs'
GO
nameminimum maximum config_value run_value
Database Mail XPs011 1
EXECUTE msdb.dbo.sysmail_help_account_sp
--no output --
Is there any SMTP server coming into picture???
If so, What 's happening behind??
November 25, 2009 at 6:29 am
SQL Mail and SQLAgentMail establish a MAPI connection with a mail host. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server or a Post Office Protocol 3 (POP3) server.
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.
Note:
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.
November 25, 2009 at 6:41 am
Why don't you use the new, easier email functionality ? sp_send_dbmail
xp_sendmail will be removed at some point, so probably not a good idea to use it if possible.
November 27, 2009 at 7:51 am
What is the difference between xp_sendmail and sp_send_dbmail?
November 27, 2009 at 9:06 am
xp_sendmail uses MAPI to send email, and sp_send_dbmail uses only SMTP.
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..
Basically I have had too many bad experiences with SQL Mail to use it when I don't have to..
CEWII
November 27, 2009 at 9:37 am
Hi
Please read this article to expand the topic.
http://www.zimbio.com/SQL/articles/1204/Database+Mail+vs+SQL+Mail+SQL+Server+2005
November 27, 2009 at 9:51 am
That was a pretty good article. And I always like articles that agree with me..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply