Stored Procedure using xp_sendmail Hangs

Stored Procedure using xp_sendmail Hangs

Post by Angela Shinkl » Wed, 20 Mar 2002 03:09:10



I am running :

Microsoft SQL Server  2000 - 8.00.534 (Intel X86)
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)SQL 2000
with service pack 2

Connecting to an Exchange Server.

It seem to run fine for a period of time.  Then running the exact same
stored procedure will cause it to hang.

When I look at the log in sqlserver I see this

*Stack Dump being sent to g:\MSSQL7\log\SQL00063.dmp
2002-03-18 10:05:06.82 spid75    Error: 0, Severity: 19, State: 0
2002-03-18 10:05:06.82 spid75    SqlDumpExceptionHandler: Process 75
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process..
*
****************************************************************************
***
*
* BEGIN STACK DUMP:
*   03/18/02 10:05:06 spid 75
*
*   Exception Address = 0607CB92 (LpValFindProp + 00000122 Line 0+00000000)
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
*   Access Violation occurred reading address D72D863F
* Input Buffer 80 bytes -
*  EXEC  p_PHONE_CHANGE_REQUEST_EMAIL
*
*
*  MODULE                          BASE      END       SIZE
* sqlservr                       00400000  00B19FFF  0071a000
* ntdll                          77F60000  77FBDFFF  0005e000
* KERNEL32                       77F00000  77F5DFFF  0005e000
* ADVAPI32                       77DC0000  77DFEFFF  0003f000
* USER32                         77E70000  77EC4FFF  00055000
* GDI32                          77ED0000  77EFBFFF  0002c000
* RPCRT4                         77E10000  77E66FFF  00057000
* OPENDS60                       41060000  41065FFF  00006000
* MSVCRT                         78000000  78043FFF  00044000
* UMS                            41070000  4107CFFF  0000d000
* SQLSORT                        42AE0000  42B6FFFF  00090000
* MSVCIRT                        780A0000  780B1FFF  00012000
* sqlevn70                       41080000  41086FFF  00007000
* rpcltc1                        77BF0000  77BF6FFF  00007000
* NETAPI32                       028C0000  028F9FFF  0003a000
* NETRAP                         02900000  02908FFF  00009000
* SAMLIB                         02910000  0291CFFF  0000d000
* ENUdtc                         02AB0000  02AC6FFF  00017000
* ole32                          02AD0000  02B86FFF  000b7000
* XOLEHLP                        02B90000  02B98FFF  00009000
* MTXCLU                         02BA0000  02BACFFF  0000d000
* VERSION                        02BB0000  02BBAFFF  0000b000
* SHELL32                        02BC0000  02CFBFFF  0013c000
* COMCTL32                       02D00000  02D89FFF  0008a000
* LZ32                           02D90000  02D97FFF  00008000
* ADME                           02DA0000  02DB2FFF  00013000
* DTCUtil                        02DC0000  02DC9FFF  0000a000
* DTCTRACE                       02DD0000  02DD6FFF  00007000
* CLUSAPI                        02DE0000  02DECFFF  0000d000
* RESUTILS                       02DF0000  02DF9FFF  0000a000
* MSDTCPRX                       02E00000  02E30FFF  00031000
* DTCCM                          02E40000  02E5DFFF  0001e000
* SSNETLIB                       035B0000  035C5FFF  00016000
* WSOCK32                        035D0000  035D7FFF  00008000
* WS2_32                         035E0000  035F3FFF  00014000
* WS2HELP                        03600000  03606FFF  00007000
* SSNMPN70                       03680000  03685FFF  00006000
* security                       03CB0000  03CC1FFF  00012000
* msapsspc                       03CD0000  03CE0FFF  00011000
* MSVCRT40                       03CF0000  03D04FFF  00015000
* schannel                       03D20000  03D40FFF  00021000
* MSOSS                          03D50000  03D74FFF  00025000
* CRYPT32                        03D80000  03DDDFFF  0005e000
* msnsspc                        03DE0000  03DFEFFF  0001f000
* digest                         03E00000  03E0DFFF  0000e000
* rnr20                          03E10000  03E1DFFF  0000e000
* msafd                          03E60000  03E6EFFF  0000f000
* wshtcpip                       03EB0000  03EB8FFF  00009000
* SSmsLPCn                       03F40000  03F46FFF  00007000
* SSmsRP70                       03F50000  03F57FFF  00008000
* rpclts1                        03FF0000  03FF5FFF  00006000
* RpcLtScm                       04080000  0408AFFF  0000b000
* MSWSOCK                        04090000  040A4FFF  00015000
* RpcLtCcm                       04170000  0417EFFF  0000f000
* sqloledb                       046F0000  04768FFF  00079000
* OLEAUT32                       04770000  04801FFF  00092000
* MSDART                         04810000  04832FFF  00023000
* comdlg32                       04840000  04871FFF  00032000
* MSDATL3                        04880000  04895FFF  00016000
* oledb32                        04F30000  04F9EFFF  0006f000
* OLEDB32R                       04FA0000  04FB0FFF  00011000
* xpstar                         04FC0000  05005FFF  00046000
* SQLRESLD                       05010000  05016FFF  00007000
* SQLSVC                         05020000  05036FFF  00017000
* ODBC32                         05040000  05074FFF  00035000
* odbcbcp                        05080000  05086FFF  00007000
* W95SCM                         05090000  0509BFFF  0000c000
* SQLUNIRL                       050A0000  050CCFFF  0002d000
* WINSPOOL                       050D0000  050E7FFF  00018000
* SHFOLDER                       050F0000  050F7FFF  00008000
* odbcint                        05240000  05255FFF  00016000
* NDDEAPI                        05260000  05266FFF  00007000
* SQLSVC                         05270000  05275FFF  00006000
* xpstar                         05280000  05288FFF  00009000
* SQLFTQRY                       052B0000  052E1FFF  00032000
* xpsqlbot                       05B00000  05B05FFF  00006000
* xplog70                        05B80000  05B91FFF  00012000
* xplog70                        05BA0000  05BA3FFF  00004000
* sqlmap70                       06030000  06059FFF  0002a000
* MAPI32                         06060000  0613CFFF  000dd000
* MPR                            06140000  06150FFF  00011000
* GAPI32                         06160000  06177FFF  00018000
* EMSABP32                       06450000  0646BFFF  0001c000
* MSPST32                        06610000  066A1FFF  00092000
* EMSUI32                        066B0000  066D0FFF  00021000
* WMSUI32                        066E0000  067FFFFF  00120000
* EMSMDB32                       06890000  06913FFF  00084000
* odbccp32                       06BA0000  06BB9FFF  0001a000
* dbghelp                        07DF0000  07E1CFFF  0002d000
*
*        Edi: D72D863F:
*        Esi: 06294154: C840A7DC  1A1042C0  0008B9B4  82E12F2B  00000001
00000000
*        Eax: D72D8637:
*        Ebx: 062D1508: 00000000  00000015  00000000  00000000  00000094
00671528
*        Ecx: 00000010:
*        Edx: 06294154: C840A7DC  1A1042C0  0008B9B4  82E12F2B  00000001
00000000
*        Eip: 0607CB92:
*        Ebp: 00000003:
*      SegCs: 0000001B:
*     EFlags: 00010206: 0074004D  005C0073  0069004C  003B0062  003A0043
0050005C
*        Esp: 06D38E28: 06296AFC  062524E8  00000000  06D38F30  06252720
06251DC8
*      SegSs: 00000023:
*
****************************************************************************
***
* --------------------------------------------------------------------------
-----
* Short Stack Dump
* 0607CB92 Module(MAPI32+0001CB92) (LpValFindProp+00000122)
* --------------------------------------------------------------------------
-----

Any Ideas what could be causing this?

Angela Shinkle
Sr. Database Analyst
Aspen Medical Group
Ashin...@aspenMed.org

 
 
 

Stored Procedure using xp_sendmail Hangs

Post by Neil Pik » Wed, 20 Mar 2002 06:07:18


Angela - several people have reported AV's with the SQL Mail from SP2.  Replace
sqlmap70.dll with the one from SP1 and see how you go

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 
 
 

Stored Procedure using xp_sendmail Hangs

Post by Michael Bourg » Fri, 05 Apr 2002 05:08:20



> Angela - several people have reported AV's with the SQL Mail from SP2.  Replace
> sqlmap70.dll with the one from SP1 and see how you go

Howdy, all.  I'm having what looks to be the same issue, and I'm
planning on replacing the server.  Since I'm a DBA, not a server
admin, I have no idea how to go about replacing it.  I read another
message that mentioned unregistering the old DLL and registering the
new DLL.  How do you do that?
 
 
 

Stored Procedure using xp_sendmail Hangs

Post by Paritosh Patha » Fri, 05 Apr 2002 07:26:49


Here it is.

1. Install SQL Server 2000 Service Pack 2. Do not proceed any further until
   SQL Server 2000 Service Pack 2 is successfully installed.  If already
installed skip to step 2

2. Shut down the Microsoft SQL Server and SQL Server Agent services.

3. Make a backup copy of the Sqlmap70.dll file from the <install path for
this SQL Server
   instance>\BINN directory.

4. Copy the Sqlmap70.dll file from the SQL2000 SP1  into
   <install path for this SQL Server instance>\BINN.

5. Start the Microsoft SQL Server and SQL Server Agent services.

6. Test the scenario for the bug that is fixed in this build to verify that
your
   problem is resolved.

7. If for any reason you encounter a problem with the SP1 build, you may go
back to the
   previous build by restoring the files you backed up in step 3.

-Paritosh



Quote:> > Angela - several people have reported AV's with the SQL Mail from SP2.
Replace
> > sqlmap70.dll with the one from SP1 and see how you go

> Howdy, all.  I'm having what looks to be the same issue, and I'm
> planning on replacing the server.  Since I'm a DBA, not a server
> admin, I have no idea how to go about replacing it.  I read another
> message that mentioned unregistering the old DLL and registering the
> new DLL.  How do you do that?

 
 
 

Stored Procedure using xp_sendmail Hangs

Post by Michael Bourg » Fri, 05 Apr 2002 22:36:00



> Here it is.

Cool!  Thanks.  The one issue I was concerned about was having to
unregister (using regsvr32) the old file and register the new one.
(It wouldn't unregister).

Just FYI, if you swap out the files (after the service is stopped) and
reboot, it will use the new DLL (instead of trying to re-use the old
one).

Michael

 
 
 

1. Stored Procedure using xp_sendmail hangs

I have a stored procedure that is called every day at 5:30 to send a batch
report using xp_sendmail.  In the procedure there are really three things
that happen:  1)  I do a simple SELECT query that does a count of items in
the batch.  2)  Using a variable, a simple ASCII report is formatted
including the numbers from step #1.  3)  Using xp_sendmail, the report is
sent.  The problem I have is that it works great  for about 30-45 days and
then at random it will hang when this procedure is called and the mail will
never be sent.  If I put in some PRINT statements for debugging I can see
that step #1 is never executed.  It acts as if the stored procedure suddenly
becomes corrupt.  The only fix that I've discovered is to reboot the
server - YUCK!  Does anyone have a cure?  Or maybe a better workaround?


Thank you,

Ian

2. Common Dialog control breaks DB connection

3. Stored Procedure using XP_SENDMAIL

4. pgsql/src/backend/utils/adt (timestamp.c date.c)

5. Using send mail xp_sendmail stored procedure

6. Pass Through query between SQL 7 and Analysis Services

7. urgent : using xp_sendmail extended stored procedure

8. Problem importing data from Paradox 5.x to SQL 2000

9. Sending custom Outlook forms using xp_sendmail extended stored procedure

10. Stored Procedure using XP_SENDMAIL

11. Exchange hangs when using xp_sendmail

12. SQL Mail hangs after using xp_sendmail

13. using SQL stored procedure results in stored procedure