Yes, I have 1216 distinct values in the in clause.
I have just tried with isql and it seems to be working fine.
Previously I used interactive sql.
Thanks a lot for your replies.
Post by Mark A. ParsonsIn your sample queries you have a comment ... "--numbers
1216" ... what does this mean? Does this mean you have
1216 different entries in the 'in ()' clause?
Are you using isql? If not, do you get the same
limitation (500 records) when using isql? [just a stab in
the dark; want to rule out any issues with the front-end
application, especially if the 'in()' clause is quite
large]
Do you see this same limitation (500 records) for
SELECT/INTOs for other source tables?
Do you see this same limitation (500 records) if you run
the SELECT/INTO against the same source table (x.y.z) but
with a different where clause? For example, replace the
'in()' clause with a 'between' clause known to return more
than 500 records. Another example, remove the 'in()'
clause, preface the query with 'set rowcount XXX' (where
XXX > 500), and run the query. [assuming a large 'in()'
clause list ... wondering about a problem with how the
dataserver is handling/processing said 'in()' clause
list]
Assuming the 'in()' clause is quite large (eg, 1216
values), what happens if you place the 'in()' clause
values in a table and drive your query with a join to
said table? [again, wondering if there's a bug in the
dataserver when working with a large 'in()' clause]
Post by unknownHi Mark,
If we do a select or select count(*) result is 1212.
Only when the same query is changed to a "select into"
referencing the same table (everything remains the same,
there is no view), the affected rows change to 500.
There are no login triggers and since the select query
works fine, there shouldn't be any problem with
rowcount. It was never set.
select * from x.y.z
where sequence_num in (.............) --numbers 1216
returns 1212 records
select * into tempdb..z from x.y.z
where sequence_num in (.............) --numbers 1216
returns 500 records
Regards
Humayun Manzer
Post by Mark A. ParsonsWhat happens if you remove the INTO clause and re-run
the >> SELECT? Do you get 500 or 1212 rows in the result
set? >> If you get 500 ... a couple possibilities come to
mind >> ...
Post by unknownPost by Mark A. Parsons- you've issued a 'set rowcount 500' command at some
point >> (perhaps in a login trigger?)
Post by unknownPost by Mark A. Parsons- the SELECT references a view which contains a 'top
500' >> clause in the view's SELECT list
Post by unknownPost by Mark A. Parsons----------------------
FWIW, you can check a spid's current 'set rowcount
======================
dbcc traceon(3604)
go
dbcc pss(0,<spid>,0)
go
======================
In the resulting output you want to look for the value
assigned to the 'prowcnt' variable.
For example, after running 'set rowcount 98765' I run
======================
... snip ...
prowcnt=98765 pcurcmd=317 (DBCC_CMD)
pstatlist=0x0 pseqstat=16 (0x0010
(SEQ_REPSPROC_CHECKED))
... snip ...
======================
Post by unknownHi all,
This is for ASE 15.0.3, I am not sure if it is a bug
or >>> I am missing something.
Post by unknownPost by Mark A. ParsonsPost by unknownI was trying to "select into" into a tempdb table and
everytime only 500 rows were affected. Actually it
should be 1212 rows.
Not knowing what is causing this,
I split my where condition restricting 400 records
each >>> time so making three queries rather than one,
then >>> finally I ended up creating 3 tables by running 3
select >>> into queries. I later did a bcp out of the
other two >>> tables into one table.
care >>> of it?
Post by unknownPost by Mark A. ParsonsPost by unknownI am sure it has nothing to do with partition as there
is only one partition i.e. the default one.
Any ideas?
Thanks and Best Regards