July 30, 2009 at 12:44 pm
I have a Stored Procedure calling the Send_mailHTML stored procedure I loaded from this site, which I think is from MS. That way it sends an HTML email based on a query. I could not use SP_CDOSYSMAIL, it sends email as html but not query results. Every so often, usually a few days to a week, I get the following error. I am including the SP, which I modified, and the errors.
This is SQL Server 2000 - 8.00.2050
Windows NT 5.2 (build 3790: Service Pack 2)
The error:
SqlDumpExceptionHandler: Process 71 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/30/09 07:01:43 spid 71
*
* Exception Address = 0840E5C6 (DllUnregisterServer + 0001022C Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 42 bytes -
* EXECUTE xspiArDoc_New
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* MSVCP71 7C3A0000 7C41AFFF 0007b000
* MSVCR71 7C340000 7C395FFF 00056000
* opends60 41060000 41065FFF 00006000
* SHELL32 7C8D0000 7D0D3FFF 00804000
* msvcrt 77BA0000 77BF9FFF 0005a000
* GDI32 77C00000 77C48FFF 00049000
* USER32 77380000 77411FFF 00092000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* comctl32 77420000 77522FFF 00103000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02C90000 02CE7FFF 00058000
* AUTHZ 02CF0000 02D03FFF 00014000
* COMRES 03020000 030E5FFF 000c6000
* ole32 030F0000 03223FFF 00134000
* XOLEHLP 03230000 03235FFF 00006000
* MSDTCPRX 03240000 032B7FFF 00078000
* Invalid Address 032C0000 03320FFF 00061000
* Invalid Address 03330000 03348FFF 00019000
* VERSION 03350000 03357FFF 00008000
* WSOCK32 03360000 03368FFF 00009000
* WS2_32 03370000 03386FFF 00017000
* WS2HELP 03390000 03397FFF 00008000
* OLEAUT32 033A0000 0342BFFF 0008c000
* CLUSAPI 03470000 03481FFF 00012000
* RESUTILS 03490000 034A2FFF 00013000
* USERENV 034B0000 03573FFF 000c4000
* secur32 03580000 03592FFF 00013000
* mswsock 035B0000 035F0FFF 00041000
* DNSAPI 03600000 0362EFFF 0002f000
* winrnr 03670000 03676FFF 00007000
* WLDAP32 03680000 036ADFFF 0002e000
* rasadhlp 036D0000 036D7FFF 00008000
* SSNETLIB 00E50000 00E65FFF 00016000
* NTMARTA 00EA0000 00EC1FFF 00022000
* SAMLIB 03EC0000 03ECEFFF 0000f000
* security 041C0000 041C3FFF 00004000
* hnetcfg 041D0000 04228FFF 00059000
* wshtcpip 04680000 04687FFF 00008000
* SSmsLPCn 04150000 04157FFF 00008000
* SSnmPN70 04160000 04166FFF 00007000
* ntdsapi 04190000 041A4FFF 00015000
* kerberos 04990000 049E7FFF 00058000
* cryptdll 049F0000 049FBFFF 0000c000
* MSASN1 04A10000 04A21FFF 00012000
* rsaenh 00F00000 00F2EFFF 0002f000
* PSAPI 00F30000 00F3AFFF 0000b000
* SQLFTQRY 04370000 04395FFF 00026000
* xpsp2res 10000000 102C4FFF 002c5000
* CLBCatQ 043A0000 04422FFF 00083000
* sqloledb 04430000 044B0FFF 00081000
* MSDART 044C0000 044D9FFF 0001a000
* MSDATL3 044E0000 044F4FFF 00015000
* oledb32 04F00000 04F78FFF 00079000
* OLEDB32R 04640000 04650FFF 00011000
* ATHPRXY 04660000 04667FFF 00008000
* msv1_0 05110000 05136FFF 00027000
* iphlpapi 05140000 05159FFF 0001a000
* xpsqlbot 05100000 05105FFF 00006000
* xpstar 05190000 051DCFFF 0004d000
* SQLRESLD 051E0000 051EBFFF 0000c000
* SQLSVC 051F0000 0520AFFF 0001b000
* ODBC32 05210000 0524CFFF 0003d000
* COMCTL32 05250000 052E6FFF 00097000
* comdlg32 052F0000 05339FFF 0004a000
* odbcbcp 05350000 05355FFF 00006000
* W95SCM 05360000 0536CFFF 0000d000
* SQLUNIRL 05370000 0539CFFF 0002d000
* WINSPOOL 053A0000 053C6FFF 00027000
* SHFOLDER 053D0000 053D8FFF 00009000
* odbcint 05530000 05546FFF 00017000
* NDDEAPI 05660000 05666FFF 00007000
* SQLSVC 05670000 05675FFF 00006000
* xpstar 05680000 05688FFF 00009000
* xplog70 05340000 0534EFFF 0000f000
* xplog70 05720000 05724FFF 00005000
* sqlmap70 05B10000 05B3CFFF 0002d000
* MAPI32 05B40000 05B5EFFF 0001f000
* MSMAPI32 05B60000 05CBEFFF 0015f000
* MAPIR 05FE0000 0609FFFF 000c0000
* mso 064A0000 0705BFFF 00bbc000
* contab32 07060000 0707FFFF 00020000
* EMSABP32 070D0000 07113FFF 00044000
* MPRAPI 07240000 07258FFF 00019000
* ACTIVEDS 07260000 07292FFF 00033000
* adsldpc 072A0000 072C6FFF 00027000
* credui 072D0000 072FDFFF 0002e000
* ATL 07300000 07317FFF 00018000
* rtutils 07320000 0732BFFF 0000c000
* SETUPAPI 07330000 07439FFF 0010a000
* netman 074A0000 074E2FFF 00043000
* netshell 074F0000 076B0FFF 001c1000
* RASAPI32 076C0000 076FEFFF 0003f000
* CRYPT32 07700000 07792FFF 00093000
* rasman 077A0000 077B1FFF 00012000
* TAPI32 077C0000 077EEFFF 0002f000
* WINMM 077F0000 0781CFFF 0002d000
* WININET 07820000 078C7FFF 000a8000
* WZCSAPI 07440000 0744CFFF 0000d000
* WZCSvc 078D0000 07940FFF 00071000
* WMI 07950000 07954FFF 00005000
* DHCPCSVC 07960000 0797EFFF 0001f000
* WTSAPI32 07980000 07987FFF 00008000
* WINSTA 07990000 079A0FFF 00011000
* ESENT 079B0000 07AB4FFF 00105000
* EMSMDB32 08020000 080CFFFF 000b0000
* odsole70 082B0000 082C0FFF 00011000
* SXS 08850000 0890BFFF 000bc000
* DBNETLIB 08B40000 08B5BFFF 0001c000
* SQLOLEDB 08D20000 08D2EFFF 0000f000
* adsldp 08250000 0827DFFF 0002e000
* schannel 057C0000 057E6FFF 00027000
* dssenh 05800000 05823FFF 00024000
* cdosys 083D0000 085CDFFF 001fe000
* urlmon 085D0000 0867DFFF 000ae000
* INETCOMM 08680000 0872BFFF 000ac000
* MSOERT2 04290000 042B2FFF 00023000
* inetres 05870000 0587DFFF 0000e000
* mlang 08770000 08803FFF 00094000
* msado15 08910000 089A1FFF 00092000
* msoeacct 089B0000 089F3FFF 00044000
* acctres 08220000 08231FFF 00012000
* msident 07170000 0717DFFF 0000e000
* msidntld 08240000 08245FFF 00006000
* PSTOREC 08730000 0873CFFF 0000d000
* usp10 08A00000 08A60FFF 00061000
* dbghelp 09610000 0970FFFF 00100000
*
* Edi: 00000000:
* Esi: 059D21BC: 00000000 00000000 00000000 00000000 00000100 00000000
* Eax: 00000000:
* Ebx: 058D1154: 058C2898 00000000 00000000 00000000 00000000 00000000
* Ecx: 059D21BC: 00000000 00000000 00000000 00000000 00000100 00000000
* Edx: 0016E4D7: 00000000 00000000 00000000 00000000 00000000 00000000
* Eip: 0840E5C6: 5150088B 50F0458D 0AE8CE8B FFFFFFFA DDE80476 83FFFEA2
* Ebp: 09E0F7A0: 09E0F7B8 0840E672 059D21BC 09E0F7C8 0846423D FFFFFFFF
* SegCs: 0000001B:
* EFlags: 00010286: 0050005F 004F0052 00450043 00530053 0052004F 003D0053
* Esp: 09E0F78C: 059D2058 059D21BC 09E0F7AC 08464180 FFFFFFFF 09E0F7B8
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 0840E5C6 Module(cdosys+0003E5C6) (DllUnregisterServer+0001022C)
* 0840E672 Module(cdosys+0003E672) (DllUnregisterServer+000102D8)
* 0840E71E Module(cdosys+0003E71E) (DllUnregisterServer+00010384)
* 083F9DD7 Module(cdosys+00029DD7) (DllCanUnloadNow+00000DAF)
* 083FA682 Module(cdosys+0002A682) (DllCanUnloadNow+0000165A)
* 083FA704 Module(cdosys+0002A704) (DllCanUnloadNow+000016DC)
* 082B4962 Module(odsole70+00004962) (sp_OAGetErrorInfo+000002F2)
* -------------------------------------------------
Send_mailhtml
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_send_MailHtml
@SmtpServer varchar(128),
@From varchar(128),
@To varchar(128),
@cc varchar(128),
@BCc varchar(128),
@Subject varchar(124)=" ",
@Query varchar(4000) = " "
/***
* Date: March 2008
* Author: daniel.eyer@free.fr
* Project: Just for fun!
* Location: Any database
* Permissions: PUBLIC EXECUTE
*
* Description: Send query result as HTML Mail
*
*
***/
AS
--Mail declaration
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--HTML declaration
declare @Columns varchar(8000)
declare @ColHeader varchar(8000)
Declare @SqlCmd varchar(8000)
Declare @HTMLBody varchar(8000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SmtpServer
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
If(@Cc Is Not Null)
Exec @hr = sp_OASetProperty @iMsg, 'Cc', @cc
If(@BCc Is Not Null)
Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
-- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
/*************************************************************************/
-- drop temporary tables used.
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
DROP TABLE ##TEMPhtml1
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')
DROP TABLE ##TEMPhtml2
-- prepare query
set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'
execute (@SqlCmd)
--Prepare columns details
SELECT @columns =
COALESCE(@columns + ' + ''
'RTrim(convert(varchar(100),isnull(' + column_name +','' '')))'
FROM tempdb.information_schema.columns
where table_name='##tempHTML1'
--SELECT * FROM ##TEMPHTML1
--PRINT @COLUMNS
--Prepare column Header
set @colHeader = '
SELECT @colHeader = @colHeader + '
'
FROM tempdb.information_schema.columns where table_name='##tempHTML1'
set @colHeader=@colHeader + '
'
--prepare final output
set @SqlCmd =
'Select ''
@columns +
' ''
'' into ##tempHTML2 from ##tempHTML1 '
execute( @SqlCmd)
--set @finalhtmlout=
set @HtmlBody =
'
]
from ##tempHTML2
set @HtmlBody = @HtmlBody + ' '
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @HtmlBody
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- drop temporary tables used.
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
DROP TABLE ##TEMPhtml1
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')
DROP TABLE ##TEMPhtml2
/*************************************************************************/
-- Sample error handling.
/* IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
*/
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
July 31, 2009 at 9:23 am
What service pack are you running? Should be on sp4 to correct this bug.
LMGTFY:
-- You can't be late until you show up.
July 31, 2009 at 12:47 pm
Yes, it is SP4. I am invesrtigating on getting AWE memory hotfix. I do not think we have that installed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply