Only 50000 records returned from query

Only 50000 records returned from query

am 17.04.2008 23:04:31 von laurenq uantrell

I'm using a sproc to query a SQL Server table with about 300,000 rows,
but when I query the table using a select query, it returns only
exactly 50,000 rows. The front end is an Access 2000 application. I
suspect that's the culprit even though max records is set to zero?
I must be missing something.
Thanks,
lq

Re: Only 50000 records returned from query

am 17.04.2008 23:19:42 von Rich P

Greetings,

Do you have access to the code for your SP? Maybe your table in
question really has only 50,000 rows but the sp is joining a bigger
table to this one with a left join or maybe the sp is doing a Union All
to some other tables?

If you have access to query analyzer do a

select count(*) from tbl_In_question

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Only 50000 records returned from query

am 18.04.2008 01:22:09 von Harry Skelton

Lauren Quantrell wrote:
> ...The front end is an Access 2000 application. I
> suspect that's the culprit even though max records is set to zero?
> I must be missing something.

Access does limit the records. You can try to alter the query limits
from inside access. I had the same problem with Informix 4GL years ago.
Its all about what you use to access the records.

If you VBA, you may want to use ADO and open the SQL server directly
rather than let Access to natively query via a form, et al.

Re: Only 50000 records returned from query

am 18.04.2008 02:10:40 von laurenq uantrell

Harry,
I can create the connection directly in VBA, I'll try that. Currently
the sproc is just a recordsource on a form.
lq


On Apr 17, 7:22=A0pm, Harry Skelton wrote:
> Lauren Quantrell wrote:
> > ...The front end is an Access 2000 application. I
> > suspect that's the culprit even though max records is set to zero?
> > I must be missing something.
>
> Access does limit the records. =A0You can try to alter the query limits
> from inside access. =A0I had the same problem with Informix 4GL years ago.=

> =A0 Its all about what you use to access the records.
>
> If you VBA, you may want to use ADO and open the SQL server directly
> rather than let Access to natively query via a form, et al.

Re: Only 50000 records returned from query

am 18.04.2008 04:42:07 von laurenq uantrell

I just realized there's a form level property - max records. It was
set to 50000. Mystery solved. Thanks.
lq

On Apr 17, 7:22=A0pm, Harry Skelton wrote:
> Lauren Quantrell wrote:
> > ...The front end is an Access 2000 application. I
> > suspect that's the culprit even though max records is set to zero?
> > I must be missing something.
>
> Access does limit the records. =A0You can try to alter the query limits
> from inside access. =A0I had the same problem with Informix 4GL years ago.=

> =A0 Its all about what you use to access the records.
>
> If you VBA, you may want to use ADO and open the SQL server directly
> rather than let Access to natively query via a form, et al.