Intersect

Question

Post reply

Intersect

bopeavy

SSCertifiable

Points: 5869
More actions
November 7, 2011 at 8:38 pm

#262506

Comments posted to this topic are about the item Intersect
:cool:

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

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

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

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;

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

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

BrainDonor SSCoach Points: 19254 More actions · Answer 8

2008R2 SP1 - runs without error.

2008 SP2 - 'severe error'

2005 - 'severe error'

Steve Hall
Linkedin
Blog Site

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 9

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:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 11

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

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 12

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:

Kwex Default port Points: 1407 More actions · Answer 13

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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 14

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,

Andre Guerreiro Neto

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