July 25, 2003 at 7:46 am
Having a consistent 100% occurrence of this error:
Can anyone help?
I have a large table of addresses from where I have an indexed view created which partitions the main table horizontally, so that the view contains only a filtered subset of the main table.
The clustered index is set up ok, and I am able to extract data when inner joining with 2 other tables. However, as soon as I put a search criteria in the 'WHERE' clause with a non-key value from one of the two joined tables - I get the following error when trying to 'display estimated execution plan', needless to say the query won't run either.
======================================
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite (WrapperWrite()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
======================================
The two queries are set out below - Query 1 is ok, Query 2 causes the error.
Query 1
SELECT dbo.Small_Users_View.Organisation_name,
dbo.Small_Users_Addresses_View.Address_bldg_no, dbo.Localities.Locality_Post_Town
FROM dbo.Small_Users_Addresses_View INNER JOIN dbo.Small_Users_View
ON dbo.Small_Users_Addresses_View.Address_organisation = dbo.Small_Users_View.Organisation_key
INNER JOIN dbo.Localities
ON dbo.Small_Users_Addresses_View.Address_locality = dbo.Localities.Locality_key
Query 2
As above, plus
WHERE (dbo.Localities.Locality_Post_Town LIKE 'HOLMFIRTH')
Version:-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
In SQL Server Log, full error is:-
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/25/03 14:19:41 spid 57
*
* Exception Address = 0073A428 (CIndex::SetLevel1Names + 00000014 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000008
* Input Buffer 1214 bytes -
* SELECT dbo.Small_Users_View.Organisation_name, dbo.Small_Users_Addre
* sses_View.Address_bldg_no, dbo.Localities.Locality_Post_Town FROM
* dbo.Small_Users_Addresses_View INNER JOIN dbo.Small_Users_View ON db
* o.Small_Users_Addresses_View.Address_organisation = dbo.Small_Users_View
* .Organisation_key AND dbo.Small_Users_Addresses_View.Address_postcode_
* type = dbo.Small_Users_View.Organisation_postcode_type INNER JOIN
* dbo.Localities ON dbo.Small_Users_Addresses_View.Addres
* s_locality = dbo.Localities.Locality_key WHERE (dbo.Localities.Loca
* lity_Post_Town = 'HOLMFIRTH')
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F80000 77FFAFFF 0007b000
* KERNEL32 7C4E0000 7C598FFF 000b9000
* ADVAPI32 7C2D0000 7C331FFF 00062000
* RPCRT4 77D30000 77D9DFFF 0006e000
* USER32 77E10000 77E74FFF 00065000
* GDI32 77F40000 77F7BFFF 0003c000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 78000000 78044FFF 00045000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 780A0000 780B1FFF 00012000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 243C0000 2440EFFF 0004f000
* SECUR32 7C340000 7C34EFFF 0000f000
* NETRAP 24410000 24415FFF 00006000
* SAMLIB 24420000 2442EFFF 0000f000
* WS2_32 24430000 24443FFF 00014000
* WS2HELP 24450000 24457FFF 00008000
* WLDAP32 24460000 24489FFF 0002a000
* DNSAPI 24490000 244B3FFF 00024000
* WSOCK32 244C0000 244C7FFF 00008000
* ole32 247B0000 2489BFFF 000ec000
* XOLEHLP 248A0000 248A7FFF 00008000
* MSDTCPRX 248B0000 24963FFF 000b4000
* MTXCLU 24970000 2497FFFF 00010000
* VERSION 24980000 24986FFF 00007000
* LZ32 24990000 24995FFF 00006000
* CLUSAPI 249A0000 249AFFFF 00010000
* RESUTILS 249B0000 249BCFFF 0000d000
* USERENV 7C0F0000 7C151FFF 00062000
* rnr20 782C0000 782CBFFF 0000c000
* iphlpapi 24A00000 24A12FFF 00013000
* ICMP 24A20000 24A24FFF 00005000
* MPRAPI 24A30000 24A46FFF 00017000
* OLEAUT32 24A50000 24AEAFFF 0009b000
* ACTIVEDS 24AF0000 24B1EFFF 0002f000
* ADSLDPC 24B20000 24B42FFF 00023000
* RTUTILS 24B50000 24B5DFFF 0000e000
* SETUPAPI 24B60000 24BEDFFF 0008e000
* RASAPI32 24BF0000 24C22FFF 00033000
* RASMAN 24C30000 24C40FFF 00011000
* TAPI32 24C50000 24C71FFF 00022000
* COMCTL32 24C80000 24D03FFF 00084000
* SHLWAPI 24D10000 24D74FFF 00065000
* DHCPCSVC 24D80000 24D98FFF 00019000
* winrnr 25240000 25247FFF 00008000
* rasadhlp 25250000 25254FFF 00005000
* SSNETLIB 25A20000 25A34FFF 00015000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 26070000 26073FFF 00004000
* msafd 26180000 2619DFFF 0001e000
* wshtcpip 261E0000 261E6FFF 00007000
* SSmsLPCn 26270000 26276FFF 00007000
* SSmsRP70 410E0000 410E7FFF 00008000
* mswsock 26410000 26421FFF 00012000
* ntdsapi 26470000 26480FFF 00011000
* kerberos 78280000 782B5FFF 00036000
* CRYPTDLL 264A0000 264ADFFF 0000e000
* MSASN1 264B0000 264BFFFF 00010000
* SQLFTQRY 41020000 41051FFF 00032000
* CLBCATQ 26A90000 26B15FFF 00086000
* sqloledb 26B30000 26BA2FFF 00073000
* MSDART 26BB0000 26BCEFFF 0001f000
* comdlg32 26BD0000 26C0DFFF 0003e000
* SHELL32 782F0000 78537FFF 00248000
* MSDATL3 26C10000 26C24FFF 00015000
* oledb32 26FB0000 27015FFF 00066000
* OLEDB32R 27020000 2702FFFF 00010000
* msv1_0 782D0000 782EEFFF 0001f000
* CRYPT32 273E0000 27457FFF 00078000
* xpstar 410F0000 41136FFF 00047000
* SQLRESLD 42AC0000 42AC6FFF 00007000
* SQLSVC 27490000 274A6FFF 00017000
* ODBC32 274B0000 274E0FFF 00031000
* odbcbcp 274F0000 274F5FFF 00006000
* W95SCM 41140000 4114BFFF 0000c000
* SQLUNIRL 10000000 1002CFFF 0002d000
* WINSPOOL 10030000 1004DFFF 0001e000
* MPR 10050000 10060FFF 00011000
* SHFOLDER 10070000 10077FFF 00008000
* odbcint 101C0000 101D5FFF 00016000
* NDDEAPI 101E0000 101E6FFF 00007000
* SQLSVC 101F0000 101F5FFF 00006000
* xpstar 10200000 10208FFF 00009000
* adsldp 10220000 10241FFF 00022000
* adsmsext 103F0000 10401FFF 00012000
* dbghelp 02800000 028B4FFF 000b5000
*
* Edi: 53B6ABC8: 0099149C 00000011 0000000F 53B6AE60 00000000 53B4A180
* Esi: 00000000:
* Eax: 0073A41E: 530CEC83 33F18B56 5E3957DB CB850F08 8B000000 7D8B1C46
* Ebx: 00000000:
* Ecx: 00000000:
* Edx: 53A92080: 2397A2D0 53A92010 53A76F78 05606AED 00995760 54B70EC0
* Eip: 0073A428: 0F085E39 0000CB85 1C468B00 53087D8B 000D8068 F0458900
* Ebp: 052DBE98: 052DBEA8 0073A114 54796030 53B4A180 052DC188 007948C0
* SegCs: 0000001B:
* EFlags: 00010246: 0050005C 006F0072 00720067 006D0061 00460020 006C0069
* Esp: 052DBE74: 53B6ABC8 00000002 54796030 00000003 0045459D 0073A41E
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 0073A428 Module(sqlservr+0033A428) (CIndex::SetLevel1Names+00000014)
* 0073A114 Module(sqlservr+0033A114) (CTableMetadata::SetLevel1Names+00000024)
* 007948C0 Module(sqlservr+003948C0) (CImpRuleStarJoinToIdxStrategyBase::PexprIdxBranch+00000037)
* 00795ABD Module(sqlservr+00395ABD) (CImpRuleStarJoinToIdxStrategyBase::BuildSubstitutes+00000564)
* 00476511 Module(sqlservr+00076511) (CTask_ApplyRule::Perform+0000026B)
* 004794F9 Module(sqlservr+000794F9) (CMemo::ExecuteTasks+0000013C)
* 00474C64 Module(sqlservr+00074C64) (CMemo::OptimizeQuery+000004F7)
* 00474822 Module(sqlservr+00074822) (COptContext::PexprSearchPlan+00000141)
* 004586DB Module(sqlservr+000586DB) (COptContext::PcxteOptimizeQuery+00000B38)
* 0045775B Module(sqlservr+0005775B) (CQuery::Optimize+000003F9)
* 00457536 Module(sqlservr+00057536) (CQuery::Optimize+00000030)
* 00455182 Module(sqlservr+00055182) (CCvtTree::PqryFromTree+0000029D)
* 00454F30 Module(sqlservr+00054F30) (BuildQueryFromTree+00000046)
* 00454E9D Module(sqlservr+00054E9D) (CStmtQuery::InitQuery+0000013E)
* 00495B47 Module(sqlservr+00095B47) (CStmtSelect::Init+0000008A)
* 00445835 Module(sqlservr+00045835) (CCompPlan::FCompileStep+00000AD6)
* 0044AB41 Module(sqlservr+0004AB41) (CProchdr::FCompile+00000D23)
* 0041BF21 Module(sqlservr+0001BF21) (CSQLSource::FTransform+0000034C)
* 0044CE55 Module(sqlservr+0004CE55) (CSQLStrings::FTransform+000001A1)
* 0041B8EF Module(sqlservr+0001B8EF) (CSQLSource::Execute+0000015B)
* 0048A63B Module(sqlservr+0008A63B) (language_exec+000003E1)
* 0042708C Module(sqlservr+0002708C) (process_commands+0000010E)
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* 78008454 Module(MSVCRT+00008454) (endthread+000000C1)
* 7C4E987C Module(KERNEL32+0000987C) (SetThreadExecutionState+00000227)
* -------------------------------------------------------------------------------
July 28, 2003 at 4:31 am
Do you have latest service pack installed.
Microsoft site says there were few problems with indexed views that resulted from SP1. SP2 has fixes to these.
Have a look at this site
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306467
Ignore this message if you have already tried this option.
July 28, 2003 at 10:47 am
I have SP3 installed. Looking at the knowledgebase article, I think the problem fix is for data modification in tables with indexed views on them. My problem is purely a search query.
I.m temporarily working around the problem by creating seperate tables and using triggers to keep the data intact. Would rather use indexed views and let the server engine take care of integrity.
July 28, 2003 at 3:43 pm
Had the "Connection Broken" part when SQL really gets confused in 6.5 and 7.
Did you try a "DBCC CHECKDB" just in case.
July 29, 2003 at 10:13 am
Sorry, result of DBCC CHECKDB was:-
CHECKDB found 0 allocation errors and 0 consistency errors in database.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply