To SQL Server 2005 Team:
This query works fine on SQL Server 2000:
select top 10 aename as EscrowOfficer,aomon,aoday,aoyeary2k,
aemon,aeday,aeyeary2k,aprice,aescr#,aprpt#,abra#,arecmn,
arecdy,arecyry2k,Source,absct,asrep1,asrep2,asrep3,asrep4,
asrep5,arepc1,arepc2,arepc3,arepc4,arepc5,atname as TitleOfficer, acust#,aagnts,aagntb,aempl#,titccd,titord,atiu,atmon,atday,atyeary2k,
atoff#,acnty#,areals,arealb
from SVRVMLAPORTALDB.RPStaging.dbo.inform
where aescr# in ( select aescr#
from SVRVMLAPORTALDB.RPStaging.dbo.inform
where aoyeary2k >= 2004 and
aomon between 1 and 12 and
aoday between 1 and 31 and
abra# <> 99)
or
aescr# in ( select inform.aescr#
from SVRVMLAPORTALDB.RPStaging.dbo.inform as inform
inner join SVRVMLAPORTALDB.RPStaging.dbo.invoicehdr as invoicehdr on
inform.aescr# = invoicehdr.aescr#
where invoicehdr.ipostdatey2k >= 20040101 and
inform.aoyeary2k < 2004 and
inform.abra# <> 99)
The result set I get back (abbreviated for clarity) on SQL Server 2000 is:
2 13 2004 3 15 …
MARGIE LLARINAS 9 10 2002 9 11 …
Leslie Azevedo 10 28 2004 12 2 …
Nesha Castelo 5 23 2005 8 22 …
Nesha Castelo 5 23 2005 7 29 …
Jill Stonebraker 9 21 2001 12 10 …
Jill Stonebraker 9 11 2002 10 31 …
Jill Stonebraker 10 31 2002 1 2 …
Jill Stonebraker 11 22 2002 1 29 …
Jill Stonebraker 12 19 2002 2 6 …
When I run the sub-queries individually on SQL Server 2005 they work fine. The first sub-query returns 14,604 rows, the second one returns 839 rows. When I run the main query without the Where clause on SQL Server 2005 it works fine.
When I run the entire query on SQL Server 2005 I get this error everytime:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
What is the SQL Server 2005 query processor barfing on?
Thank You.
I looked in the Event Log of the server and the error says: "A user request from the session with SPID 54 generated a fatal exception, SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory." Event_ID 17310.The mini dump looks like this:
2005-10-27 13:44:51.01 spid54 * BEGIN STACK DUMP:
2005-10-27 13:44:51.01 spid54 * 10/27/05 13:44:51 spid 54
2005-10-27 13:44:51.01 spid54 *
2005-10-27 13:44:51.01 spid54 *
2005-10-27 13:44:51.01 spid54 * Exception Address = 016F322F Module(sqlservr+006F322F)
2005-10-27 13:44:51.01 spid54 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2005-10-27 13:44:51.01 spid54 * Access Violation occurred reading address 00000000
2005-10-27 13:44:51.01 spid54 * Input Buffer 510 bytes -
2005-10-27 13:44:51.01 spid54 * select top 10 DNAME,DDATEY2K,DADATEY2K,DDESCR,DAMT,DESCR#,DBR
2005-10-27 13:44:51.01 spid54 * A#,SOURCE,DDESCR,DATTN,DHUDL# from SVRVMLAPORTAlDB.RPStaging.dbo.deta
2005-10-27 13:44:51.01 spid54 * il where dadatey2k > 20031231 and descr# in (select aescr# from S
2005-10-27 13:44:51.01 spid54 * VRVMLAPORTAlDB.RPStaging.dbo.inform where aoyeary2k >= 2004 and aomon be
2005-10-27 13:44:51.01 spid54 * tween 1 and 12 and aoday between 1 and 31 and abra# <> 99)or descr#
2005-10-27 13:44:51.01 spid54 * in (select inform.aescr# from SVRVMLAPORTALDB.RPStaging.dbo.inform info
2005-10-27 13:44:51.01 spid54 * rm inner join SVRVMLAPORTAlDB.RPStaging.dbo.invoicehdr invoicehdr on inf
2005-10-27 13:44:51.01 spid54 * orm.aescr# = invoicehdr.aescr# where invoicehdr.ipostdatey2k >=20040101
2005-10-27 13:44:51.01 spid54 * and inform.aoyeary2k < 2004 and inform.abra#<>99)
After some more digging and googling I came across this article:
http://support.microsoft.com/kb/894254
Which says:
SYMPTOMS
In Microsoft SQL Server 2000, when you run a complex query that includes a UNION ALL operator and an EXISTS clause, the query may fail. You may receive an error message that is similar to the following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
For a list of previous hotfixes, see the "Microsoft SQL Server 2000 post-Service Pack 3 or Microsoft SQL Server 2000 post-Service Pack 3a hotfixes" section in the following Microsoft Knowledge Base article: 290211 (http://support.microsoft.com/kb/290211/) How to obtain the latest SQL Server 2000 service pack
CAUSE
This issue occurs because SQL Server 2000 does not correctly determine the properties for correlated predicates after the correlated predicates are remapped.
So - I guess SQL Server 2005 has the same bug right now? Does anyone know if there is a hotfix on SQL 2005 for this issue?
Thanks.
|||What build of SQL Server 2005 are you running? Is it possible for you to post a repro script that I can run? Maybe this is fixed now.|||Select @.@.Version says:Microsoft SQL Server 2005 - 9.00.1314.06 (Intel X86) Sep 2 2005 21:10:31 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Here are a couple more example queries I have been trying:
This query works fine:
select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source
from SVRVMLAPORTAlDB.RPStaging.dbo.propty
where lescr#in( select aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.inform
where aoyeary2k>= 2004and
aomonbetween 1and 12and
aodaybetween 1and 31and
abra#<> 99)
This query works fine:
select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source
from SVRVMLAPORTAlDB.RPStaging.dbo.propty
where lescr#in( select inform.aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.informas inform
innerjoin SVRVMLAPORTAlDB.RPStaging.dbo.invoicehdras invoicehdron
inform.aescr#= invoicehdr.aescr#
where invoicehdr.ipostdatey2k>= 20040101and
inform.aoyeary2k< 2004and
inform.abra#<> 99)
However, this query does not work:
select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source
from SVRVMLAPORTAlDB.RPStaging.dbo.propty
where lescr#in( select aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.inform
where aoyeary2k>= 2004and
aomonbetween 1and 12and
aodaybetween 1and 31and
abra#<> 99)
or
lescr#in( select inform.aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.informas inform
innerjoin SVRVMLAPORTAlDB.RPStaging.dbo.invoicehdras invoicehdron
inform.aescr#= invoicehdr.aescr#
where invoicehdr.ipostdatey2k>= 20040101and
inform.aoyeary2k< 2004and
inform.abra#<> 99)
I always get the error: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. All the last query does is put the two sub-queries together with an OR operator. If it helps any, it's worth noting that the server I am running this on is a separate server from SvrVmLaPortalDB. I have a LinkedServer setup. Thanks.
Could you rewrite your query like below and see if it works? This might provide workaround for the problem temporarily.
select top 10 * from (
(
select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source
from SVRVMLAPORTAlDB.RPStaging.dbo.propty
where lescr#in( select aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.inform
where aoyeary2k>= 2004and
aomonbetween 1and 12and
aodaybetween 1and 31and
abra#<> 99)
)
union
(
select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source
from SVRVMLAPORTAlDB.RPStaging.dbo.propty
where lescr#in( select inform.aescr#
from SVRVMLAPORTAlDB.RPStaging.dbo.informas inform
innerjoin SVRVMLAPORTAlDB.RPStaging.dbo.invoicehdras invoicehdron
inform.aescr#= invoicehdr.aescr#
where invoicehdr.ipostdatey2k>= 20040101and
inform.aoyeary2k< 2004and
inform.abra#<> 99) select top 10 lescr#,apstreet#,apadd1,apadd2,apadd4,apadst,apadzip9,apar#,source from SVRVMLAPORTAlDB.RPStaging.dbo.propty where exists( select from SVRVMLAPORTAlDB.RPStaging.dbo.inform where aoyeary2k>= 2004and aomonbetween 1and 12and aodaybetween 1and 31and abra#<> 99 and or exists( select * from SVRVMLAPORTAlDB.RPStaging.dbo.informas inform innerjoin SVRVMLAPORTAlDB.RPStaging.dbo.invoicehdras invoicehdron inform.aescr#= invoicehdr.aescr# where invoicehdr.ipostdatey2k>= 20040101and inform.aoyeary2k< 2004and inform.abra#<> 99
) as t1
Or change the IN condition to EXISTS and see if it works also.
lescr# = aescr#)
and inform.aescr# = lescr#)
I hope Microsoft fixes this bug before they release SQL 2005.
Thank You!
|||I am getting the same error on a simple DELETE statement, Any body have any idea ?
DELETE FROM tblUser
WHERE userID = 64727
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
|||Hi
Can it be that this error exists when moving a backup from SQL Server 2000 to SQL 2005 Developer?
No comments:
Post a Comment