Intersect

  • Comments posted to this topic are about the item Intersect

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • erm...just me or does this query in fact work?

    name fileid

    master 1

    Ran it in Denali and seems fine... 😎

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Query runs just fine in SQL 2008 R2 as well.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Very interesting question, but 1) version-specific; 2) with wrong explanation.

    I could reproduce the error only in SQL Server 2005. In SQL Server 2008 R2, the query works fine.

    This situation must be a bug in SQL Server 2005. For some reason, SQL Server 2005 raises an error with severity level = 20 (which means a fatal severity level, the client connection is terminated after receiving that message), and writes the following message to Event Log:

    A user request from the session with SPID 60 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

    These queries work fine in SQL Server 2005, although they are very similar to the QotD query:

    select fileid, name from sysfiles

    intersect

    select cast(file_id as int), name from sys.master_files

    where file_id in('1')

    order by 2;

    select file_id, name from sys.master_files

    where file_id in('1')

    intersect

    select fileid, name from sysfiles

    order by 2;

    select fileid, name from sysfiles

    intersect

    select file_id + 0, name from sys.master_files

    where file_id in('1')

    order by 2;

  • SQLRNNR (11/7/2011)


    Query runs just fine in SQL 2008 R2 as well.

    I ran it in SQL Server 2008R2, and I got the severe error.

    The plot thickens... 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Here's a screenshot (in attachment).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (11/8/2011)


    SQLRNNR (11/7/2011)


    Query runs just fine in SQL 2008 R2 as well.

    I ran it in SQL Server 2008R2, and I got the severe error.

    The plot thickens... 🙂

    Maybe it was fixed with SP1?? I see you are running RTM and I have SP1 installed...:hehe:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (11/8/2011)


    Koen Verbeeck (11/8/2011)


    SQLRNNR (11/7/2011)


    Query runs just fine in SQL 2008 R2 as well.

    I ran it in SQL Server 2008R2, and I got the severe error.

    The plot thickens... 🙂

    Maybe it was fixed with SP1?? I see you are running RTM and I have SP1 installed...:hehe:

    Ah, keen observation 🙂

    Brand new development server, so SP1 isn't installed yet.

    This reminds me that I should pick this up with the DBA's.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 2008R2 SP1 - runs without error.

    2008 SP2 - 'severe error'

    2005 - 'severe error'

  • WORD OF WARNING,

    Having guessed the right answer I then decided to run it on our SQL 2005 (production) server.

    After restarting the service this line was found in the log.

    11/08/2011 08:13:34,spid80,Unknown,SqlDumpExceptionHandler: Process 80 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    11/08/2011 08:13:34,spid80,Unknown,***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0007.txt

    11/08/2011 08:13:34,spid80,Unknown,Using 'dbghelp.dll' version '4.0.5'

    Guess that will teach me not to use the test server :Whistling:

  • philip.cullingworth (11/8/2011)


    WORD OF WARNING,

    Having guessed the right answer I then decided to run it on our SQL 2005 (production) server.

    After restarting the service this line was found in the log.

    11/08/2011 08:13:34,spid80,Unknown,SqlDumpExceptionHandler: Process 80 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    11/08/2011 08:13:34,spid80,Unknown,***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0007.txt

    11/08/2011 08:13:34,spid80,Unknown,Using 'dbghelp.dll' version '4.0.5'

    Guess that will teach me not to use the test server :Whistling:

    It is always a good idea to run scripts from the internet on the master database of the production server 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Without ORDER BY it runs fine, also it works fine like this:

    use master

    select * from

    (

    select fileid, name from sysfiles intersect

    select file_id, name from sys.master_files

    where file_id in('1')

    ) as x

    order by 2

    So it is obviously something wrong arround order by.

    But I still don't understand the reason of the error. In both msdn docs 2005 and 2008

    http://msdn.microsoft.com/en-us/library/ms191523.aspx

    it states that: "ORDER BY is allowed only at the end of the statement. It cannot be used within the individual queries that make up the statement. "

    Regards,

    Iulian

  • Koen Verbeeck (11/8/2011)


    It is always a good idea to run scripts from the internet on the master database of the production server 😀

    Lesson Learnt. It won't be happening again.:blush:

  • Nice question - got it wrong today 'cos I thought the response "query out of order" referred to a syntax error and not otherwise. It parsed successfully but blew up when you run with the ORDER BY 2 clause.

    Thanks.

  • Koen Verbeeck (11/8/2011)


    It is always a good idea to run scripts from the internet on the master database of the production server 😀

    :laugh: :laugh:

    It crashes here are well and I'm on SQL Server 2008 R2 RTM.

    The error message made me scared for one second. It's one of those messages that make users disconnect the power cable just in case.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply