SQL Mail generates Demo 19 errors and Hangs
-
We have a 2000 server -- 5.00.2195 SP4 running Sql Server 2000 SP3, and are experiencing a strange SQL Mail issue.
SQL Mail is configured using an MS Exchange profile and runs for anywhere from a few days to a few weeks. However, during
this time, it generates Demo 19 errors in SQL Server when some (but not all) of our emails are sent out. The short
stack dumps for all of these errors are identical --
* Short Stack Dump
* 77F8206B Module(ntdll+0000206B) (RtlEnterCriticalSection+0000000B)
* 77E14605 Module(USER32+00004605) (TranslateMessageEx+00000605)
* 77E15B77 Module(USER32+00005B77) (DispatchMessageA+0000000B)
* 7E816608 Module(UNKNOWN+00000000)
Eventually, SQL Mail fails completely and hangs and only a complete restart of the server (not "sp_stopmail")
will get it working again. Can anyone tell me what is causing this issue? I'm getting the impression it's probably
some kind of configuration problem, but I'm not knowledgible enough to know where to begin. Any pointers would be a
big help.
Also, there's an interesting "side issue" I'd like to understand -- we have a table which contains several Insert and Update
triggers that send out emails using xp_SendMail. When the SQL Mail server hangs, "select" statements on this table
which use a where clause hang-- but those without a where clause execute fine. It's pretty clear that SQL Server gets
hung up on the xp_sendmail reference in the trigger when it attempts to do a select with a "where" clause, but not when it
doesn't have a where clause. Can anyone explain this behavior to me?
I really appreciate any help on this one -- we're all stumped!
-
what version of outlook do you have installed on the SQL server?
MVDBA
-
have previously seen KB articles about this, relating to the exchange server being unavailable for the address book lookup.
i'd definatley recommend making sure your emails specify the full address (not the address book name) and make sure your exchange server is available at the time.
if not then i'm a little stumped.
MVDBA
-
I agree with Michael, pretty weird behavior and may be worthy of a call to MS.
On a side note, I would recommend having your mail processing within triggers, stored procedures, etc process to a table and then use xp_sendmail through a job to process the emails out of the table on a regular interval. Pretty easy to set up and it allows for applications to continue running even when xp_sendmail has issues. Ultimately I would hate to hang my application functionality on xp_senmail uptime. Too risky.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Thank you all so much for the quick replies!
We seem to have two versions of Outlook on the server, Outlook contained in Office 2000 SR1 Pro (9.0.6926 SP3) and Outlook Express 6, 6.00.2800.1123..
Many of the emails we're sending that generate the errors do contain the full email addresses for recipients. I'll have to review some of them to see if there are any exceptions -- I don't know of any examples where we use partial names, but I will look into that possibility. I definately want to know what is different about those emails which generate the SEV 19 errors, and those which never do. I would think that if it were a simple connectivity issue, we would not so consistently see the issue with some emails, and never see it with others...
I do like the idea of getting the sendmail commands out of the triggers. I must admit I never would have thought that emails sent in a trigger would stop SQL Server from executing (some) simple select queries against a table! But I learn something every day
. The idea is definately a good one, and would increase the reliability and dependability of that particular application significantly. I did notice this morning that while the SQL Mail profile lists "MS Exchange Settings", there is an SMTP routing address in the Exchange administrator. I understand SMTP servers are less reliable for use with Sql Mail, perhaps this is contributing. Unfortunately, I am an applications developer and not a network admin or DBA-- Attempting to trouble shoot this problem is forcing me to learn things at a quick rate! I do like that part, but I'm afraid some important clues are eluding me because of my lack of experience.
Thanks again for the help-- I really appreciate the input.
-
in the meantime can i suggest a very easy alternative
i use a program called blat.exe which is an smtp mail client.
if you want to send an email (and this is particularly helpfull if you are running lotus notes) you can have the trigger or job create a text file with the contents of the email and then use xp_cmdshell to execute blat.exe with the required parameters
MVDBA
-
How about an easy alternative that doesn't require calling a application external to SQL Server? Try xp_smtp_sendmail from http://www.sqldev.net
Take a look at Bill Wunder's series of articles for replacing SQL Mail http://b.wunder.home.comcast.net/
--------------------
Colt 45 - the original point and click interface
-
I've found a good web cast from Microsoft that discusses this sort of issue --
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc091301/WCT091301.asp
It's old, but has some good information -- I'm currently looking at the possibility that our extended MAPI dll's are out of sync. Also, There's a knowledge base article, 839405, that discusses SQL Mail generating access violations on multiprocessor computers, which our's is.
It would make testing my "solutions" much easier if I could generate these errors "on demand", but I haven't managed to isolate the cause sufficiently. I have gone ahead and made some MAPI32.dll changes as indicated in the web cast, but I don't have any way to test if they worked except simply to wait -- not a great situation.
We are planning a massive server rebuild/ upgrade in the next few weeks, so I've been asked to hold off on actually changing the code so we can "see what happens" at the point of upgrade. None of our other servers ever generate this error, so everyone is "hopeful" that the upgrade will fix the problems. Of course, I'd prefer to know the cause and understand it, but sometimes in this business, fixing it is all you get.
I will post back if I discover anything interesting, and I do of course appreciate the thoughts and ideas everyone is providing. I will definately look at the coding solutions mentioned. I've become a convert on non- mapi solutions, and my future development efforts will be directed there. Besides, that server upgrade might not change anything -- and if it doesn't, well, code rewrites will be in order!
-
After two full weeks without Demo 19 errors, I can safely say "It's FIXED!!!". We have logs going back for the server well over two years, and never before have we gone longer than 9 days without the error -- and only twice did we manage that. SO----
The problem turned out to be a Mapi DLL incompatibility between the common files directory and the system32 directory. The problem is discussed in some detail toward the end of the Web Cast --
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc091301/WCT091301.asp
We fixed the problem by making sure that the MAPI32 Dlls in the two directories matched. Probably the best way to acomplish this would be to upgrade Outlook to 2003, which would rewrite the MAPI32 dll. However, we did a "weekend copy" of the newer of our two dll's, restarted the SQL Server, and were good to go.
I hope this helps someone running into the same issue. It's apparently pretty common, and at least in our case, the fix turned out to be painless once implimented. It was getting there that was tough.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply