Problem with query using SQL Server 2005 September CTP

Hot Topic

Documentation / Metadata tools
Packages not executing right on production server?
PivotTable Total from Database
VS2005 and SQL Reporting Service
replication system stored procedures parameter defaults ?
I can't access cubes from excel
How would you archive a subset of your db if you had to?
replacing cursors with sql query
Excel XP - Cannot connect to OLAP 9 - Analysis Services 2005
Problem referencing a global temporary table

SQL Server

Merge Replication Using RMO
Help with Multiple connections in a CLR stored procedure
infromation about accuracy of the mining model problem
Executing trigger
Normalisation issue
AddEvent, CreateSubscription, and UpdateActiveSubscription guidance
diploying reports on browser with custom assembly
Package configuration help !
data mining and SS05
How to Drop a user from a database who owns a dbo schema and is the dbo

Problem with query using SQL Server 2005 September CTP

To SQL Server 2005 Team:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

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.




Answer this question

Problem with query using SQL Server 2005 September CTP

  • Robert McNeal

    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.


  • SupermanAlchemist

    Hi

    Can it be that this error exists when moving a backup from SQL Server 2000 to SQL 2005 Developer


  • zjorks

    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 < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    from     SVRVMLAPORTAlDB.RPStaging.dbo.propty

    where  lescr# 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 )

    )
    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.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 )
    )
    ) as t1


    Or change the IN condition to EXISTS and see if it works also.

    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 >= 2004 and

                                                    aomon between 1 and 12 and

                                                    aoday between 1 and 31 and

                                                    abra# <> 99 and
    lescr# = aescr# )

                or

                exists (        select   *

                                        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
    and inform.aescr# = lescr#)

  • Pontus Bergquist

    Wow - the first Union query works fine, but the second Exists query gives me the same error message as before.  This is really weird.  Why would the SQL 2005 query processor barf on the EXISTS and OR queries, but process the UNION query just fine

    I hope Microsoft fixes this bug before they release SQL 2005.

    Thank You!

    Smile

  • Manoj Kumar31390

    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.

  • steve17

    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.

  • LimitedSlip

    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:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

     

    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 >= 2004 and

                                                    aomon between 1 and 12 and

                                                    aoday between 1 and 31 and

                                                    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.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 )

     

     

     

    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 >= 2004 and

                                                    aomon between 1 and 12 and

                                                    aoday between 1 and 31 and

                                                    abra# <> 99 )

                or

                lescr# 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 )



    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.



  • Problem with query using SQL Server 2005 September CTP
Answer this question