[Eclipsesql-bugs] [ eclipsesql-Bugs-1597227 ] empty returns on complex query for MSSQL

[SourceForge.net <no...@so...>]

Bugs item #1597227, was opened at 2006-11-15 13:30
Message generated for change (Comment added) made by admorgan
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=1597227&group_id=132863

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: C. Trudeau (cltrudeau)
>Assigned to: Austin Morgan (admorgan)
Summary: empty returns on complex query for MSSQL

Initial Comment:
I've had some problems with a couple of complex queries not returning results from MS-SQL.  Cutting and pasting the queries into a BIRT data set, the queries work fine.  They also work inside of M$'s Query Analyzer.

The queries in question involve multiple statements (e.g. several inserts into a table variable followed by a select on that variable).  The behaviour isn't quite consistent, I've managed to get it to return a result and then re-running the same query without doing anything results in nothing.  No errors.  The status line usually reports something about rows effected, but not the number of rows that would be effected by the entire query set.  It looks like only part of a response set is being examined.

An example query that I've had problems with:

----------------------------------------

DECLARE @developers TABLE
(
	ID INT,
	UserID VARCHAR(64),
	AbrName VARCHAR(128),
	Team VARCHAR(6)
)

INSERT @developers 
	SELECT DISTINCT u.ID, u.UserID, 
		u.FirstName + ' ' 
		+ LEFT(u.Lastname, 1), 'Flash'
	FROM commonUsers u
	WHERE 
   		u.UserID IN ('alamba', 
   		'mlapasa', 'mturcotte', 'nbiln' )
   		
INSERT @developers 
	SELECT DISTINCT u.ID, u.UserID, 
		u.FirstName + ' ' 
		+ LEFT(u.Lastname, 1), 'Java'
	FROM commonUsers u
	WHERE 
   		u.UserID IN ('adumitru', 'byang', 
   		'dcresswell', 'dmaloney', 
		'ghuangfu', 'itahir', 
		'jouellette', 'klockhart', 
		'kodger', 'mjorritsma', 
		'mmaloney', 'mngo', 'nangka' )

SELECT     
    CASE s.ChangeRequest 
    	+ s.FeatureRequest * 2
	WHEN 0 THEN 'SR' 
	WHEN 1 THEN 'CR' 
	WHEN 2 THEN 'FR'
	WHEN 3 THEN 'EE'
    END AS Type, 
    s.ID, s.Subject, t.Name as Status, 
	d.AbrName, s.TargetVersion
FROM srsServiceRequest s, commonUsers u, 
	srsStatus t, @developers d
WHERE
    s.IDUserAssigned = u.ID 
    AND s.IDStatus = t.ID 
    AND t.Name NOT In 
    	( 'Resolved', 'Closed' ) 
    AND u.ID = d.ID
UNION
SELECT
	' ' as Type,
	' ' as ID,
	' ' as Subject,
	' ' as Status,
	d.AbrName,
	' ' as TargetVersion
FROM commonUsers u, @developers d
WHERE
	u.ID = d.ID
	AND u.ID NOT IN 
	( SELECT DISTINCT s.IDUserAssigned
		FROM SRSServiceRequest s,
			SRSStatus t
		WHERE 
			s.IDStatus = t.ID
			AND t.Name NOT IN 
			('Resolved', 'Closed' )
	)
Order by d.AbrName, TargetVersion, ID

----------------------------------------

Often when running the above query the status line reports "four rows effected" which looks to me like it is only reporting on the first insert.



----------------------------------------------------------------------

>Comment By: Austin Morgan (admorgan)
Date: 2008-11-02 21:53

Message:
Originally SQl Explorer was not able to properly handle multiple statements
run in one session.  This was strange because there was a identifier
telling it how to split statements.  At any rate in 3.5 this was fixed.  If
you get the newer version of SQL Explorer and the newest version of the
MSSQL extensions you should not experience this issue.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=1597227&group_id=132863