Link to home
Start Free TrialLog in
Avatar of Bearjing
Bearjing

asked on

SQL Server Hangs and generates DEMO 19 errors:

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!

Avatar of ispaleny
ispaleny
Flag of Czechia image

It is a GPF in xp_sendmail DLL.

xp_SendMail is inherited from older Sybase project. It doesn't allow send simultanously from multiple connections.

You can:
    1. Wait for a patch. Many wait for years already.
or 2. Write an process sending emails
or 3. Write a procedure using CDO objects (CDO.Message,...)

Good luck
Avatar of Bearjing
Bearjing

ASKER

This is interesting. I will be looking into this---
Just another note, it's generally a bad idea to put "notification" into a trigger for exactly this reason.  Build a "queue" table, or schedule a job to run every N minutes to process mail.  You'll end up with a record/table locked due to the trigger failing.

What version of MAPI are you using?
On the DB server we have 1.0.2518.0 in the common files directory (along with MSMapi32.dll, V. 5.5.3180.0) and in the sys32 directory we've got 1.0.2536.0. On the Exchange server we've got 1.0.2518.0 in the common files directory (w/ msmapi32 = 5.5.3121.0), and in the sys32, it's 5.5.2653.12. I'm trying to sort out which of the MAPI dll's are used when, and how the version variation impacts this situation.

I've been talking with some of the people here about getting the notifications out of the triggers. I must admit, even after several years working with SQL Server, I've never seen this happening before, and would not have thought a trigger notification would be locking the table like this. It's a "new to me" issue, but now that it's happened, it does make sence that it's a risk. I very much like the idea of getting a que table of some sort going-- this will at the very least minimize the impact of this problem.

"I've never seen this happening before, and would not have thought a trigger notification would be locking the table like this. It's a "new to me" issue, but now that it's happened, it does make sence that it's a risk"


Anytime you call anything "external" in a trigger it's a risk.  You'll find lots of people that use xp_cmdshell in a trigger to call external programs--just not a good idea.....


Anytime you call anything "external" in a trigger it's a risk.  You'll find lots of people that use xp_cmdshell in a trigger to call external programs--just not a good idea.....


I do understand this now -- but do note that in Microsoft's knowledge base documentation on triggers, their example is a trigger which includes an xp_sendmail command. This is something SQL server is "intended" -- or at least advertised -- to do, and while it clearly has it's pitfalls, it's not something out of the ordinary. I will, of course, be more careful in my own code in the future to isolate external calls, to protect from this sort of issue, and I think I'll be recoding some of the current SQL  code we use to mitigate the problems caused by larger SQL Mail  issue.

"I do understand this now -- but do note that in Microsoft's knowledge base documentation on triggers, their example is a trigger which includes an xp_sendmail command. This is something SQL server is "intended" -- or at least advertised -- "

Not to argue, but Microsofts examples also show extensive use of system tables.  Then, they turn around and tell you not to use them (rightly so).

You should also take a look at Notification Services--this is what it's slated to do instead of relying on triggers and SQLMail :)

Brett
 
I see the point Arbert-- Sometimes MS's own use of their technologies isn't perfect. I didn't write the code in question myself, I guess I'm a little defensive of it just because I know that had I been writing this app I might well have made the same choice-- and actually some of the programmers here are still using triggers in this way.  But there are definately better options, as I'm learning now. A little skepticism of MS's examples is clearly in order.

I'm looking forward to the release of 2005 for any number of reasons! In the meantime perhaps MS Messaging que can help.... I'm looking at a few options to move the mail commands elsewhere. I don't think it will take too much effort or time, and the benefit will be significant.


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.


I have requested a point refund as I've answered my own question. The request is in the community support forum.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of CetusMOD
CetusMOD
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial