| Author |
Topic |
|
Zathras
Starting Member
28 Posts |
 Posted - 2005-04-07 : 20:00:11
|
| After working with xp_sendmail in SPs for awhile now, I think it is unreliable at best. We've already installed the hotfix regarding broken sessions between exchange and sql server [url]http://support.microsoft.com/default.aspx/kb/833045?[/url], but it only takes a matter of time before xp_sendmail stops working and returns the dreaded: Server: Msg 18025, Level 16, State 1, Line 0 xp_sendmail: failed with mail error 0x80004005 What's even worse about the problems with xp_sendmail is that the error is not logged in the SQL Server logs. Anyone know how to get this error logged?My question is that has anyone else reached this conclusion and moved to something else? What are you guys using? I just read the excellent article about making a message queue at [url]http://www.sqlteam.com/item.asp?ItemID=5908[/url].I'm up for making the message queue system if that's the way to go, unless people know of better or more accepted way. Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-07 : 20:20:53
|
| To make SQL Mail more reliable, use Outlook for 2002, also known as Outlook XP. When we were running Outlook 2000, we had this reliability problem as you have mentioned. Since upgrading to the newer version per MS' advice, we have not had any reliability problems. And that was about 2 years ago. HTH.But I wouldn't use SQL Mail for an application. It should only be used for DBA type stuff like notifying DBAs of job failures and such.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-07 : 21:27:34
|
| I FULLY agree with Tara on this. NEVER use SQLMain for a mail application. It's for notifications only to alert the DBA group. If you need to email and you want to hold the messages, draw recipient lists, etc from SQL Server that's great. It's very easy to make an application that will just take the lists and messages, then email using SMTP. We had a lot of people using SQLMail for this. It's extremely inefficient and it's unreliable. It can also be a security risk depending on how the account is setup.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Zathras
Starting Member
28 Posts |
Posted - 2005-04-07 : 23:15:37
|
| What should I be using then? Outlook itself? How do I get my SP to communicate with Outlook? Can you point me in the right direction on how to do that? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-07 : 23:22:18
|
| Don't use SQL Server for it at all. Learn to develop. This is an application, not a database. If you are determined to use SQL Server for mail, then at least use SMTP mail. You can find out how to do that here:http://www.sqlteam.com/filtertopics.asp?topicID=125MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Zathras
Starting Member
28 Posts |
Posted - 2005-04-08 : 01:25:52
|
| I think there is some misunderstanding.... I'm not writing some C# or VB application that just happens to call some SPs. These are pure T-SQL stored proceedures that move & manipulate data from one database to another. They are run as part of a nightly batch process executed by the SQL Agent. The emails are generated by the SPs whenever there is a problem in the process and that manual intervention is needed. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-08 : 01:31:30
|
so you need it for alerts and notifications, i personally don't use sqlmail and create a mailing application using VB, afraid to even set up sqlmail no matter how careful you are, coz you have to use outlook  to each his own however  --------------------keeping it simple... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-08 : 01:34:44
|
| Ok. Zathras, you can use the alternate SMTP solutions for the alerts and status updates. Did you look at the link I gave you?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Zathras
Starting Member
28 Posts |
Posted - 2005-04-08 : 01:38:28
|
| Yes, I'll create the message queing system for certain. I'll see if I can get the dba to install the alternative sendmail object and give that a whirl. Thanks for you help. |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
|
|
Zathras
Starting Member
28 Posts |
Posted - 2005-04-08 : 09:14:21
|
| I need one that can do @query too. Looks like that one states it as a known limitation. I need to check if the others can do @query in the email also... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-08 : 12:33:21
|
| In response to your e-mail:SQL Mail uses Outlook as the e-mail client. To work with SQL Mail, you use xp_sendmail like you have. So xp_sendmail will become more reliable if you upgrade your Outlook client on the database server.Tara |
|
|
|
|
xp_sendmail is unreliable