?
Solved

SQL Server Hangs and generates DEMO 19 errors:

Posted on 2005-04-29
13
Medium Priority
?
265 Views
Last Modified: 2010-03-19
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!

0
Comment
Question by:Bearjing
11 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13896696
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
0
 

Author Comment

by:Bearjing
ID: 13896878
This is interesting. I will be looking into this---
0
 
LVL 34

Expert Comment

by:arbert
ID: 13897960
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?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Bearjing
ID: 13898093
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.

0
 
LVL 34

Expert Comment

by:arbert
ID: 13898237
"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.....

0
 

Author Comment

by:Bearjing
ID: 13899582

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.

0
 
LVL 34

Expert Comment

by:arbert
ID: 13899593
"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
 
0
 

Author Comment

by:Bearjing
ID: 13908635
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.


0
 

Author Comment

by:Bearjing
ID: 14048808
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.

0
 

Author Comment

by:Bearjing
ID: 14230704

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

Thanks!
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 14265092
PAQ'd, 250 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question