Mystery - select missing data; WHY?

[ljvb7]

In SQL Server2000 in our production environment, I use a SQL statement to create a view that isn't working in the latest implementation.
I isolated the problem to a select statement to retreive records from a view and a table;
Select (multiple values from table) where
View.PtType=Table.Ptype

Both (Type & PtType) are datatypes nvarchar (The view inner joins another Table for it's value;Type)
Both type tables use the values I,O,E,S,& R.

The statement works in every implementation except this one.

I’ve rebuilt the table using a copy of the working table and imported the same values.

All that return are 10 records instead of 120K.
Any ideas?

 

[fluteplr]

1st shouldn't your sql statement be in the form

select whatever from tablename, viewname
where etc.?

2nd When you retrieve data from the view and table separatly can you see the values you expect in the field?

3rd have you tried forcing a recompile of the view?
Open the view in enterprise manager make some minor change like adding a space and save it again.

 

[ljvb7]

Thanks for responding.
I switched the statement form without change.
When retrieving data from the table, view, or table joined to view seperately, I get expected data.
I copied the view into SQL Query analyzer, changed the view name +1, added a space for a new created date, and reran the query. I still get only 10 records.
I do notice that the results return pretty quickly, I'm checking to see if it only returns the first ten, though I doubt that's likely.

 

[tlbroadbent]

If you want help on the query, please post the complete query. Execute the following before the query to be sure a rowcount limit is not set.

SET ROWCOUNT 0 If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ljvb7]

Added SET ROWCOUNT 0 (no change in number returned)
SELECT * FROM DCRDeptEncounters, DictPT
WHERE DCRDeptEncounters.PtType = DictPT.PType
(I've moved the order around without any affect)

query returns 10 rows.

If I add another parameter (e.g., and PType = 'O', or the other value, I get that number back e.g., I=43,233 rows
the individual parameters return total the views total record count of 125,046.

I'm running SQL Server 2000 8.00.194