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 + '
' + column_name + ''
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 =
'
' +@colHeader
select @HtmlBody = @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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy