PROBLEM with SET ROWCOUNT

PROBLEM with SET ROWCOUNT

Post by Martin Fin » Sat, 13 Jun 1998 04:00:00



Hi

I am retrieving multiple sets of data from SQL Server 6.5 on NT Server
4.0 using Access 97 as a front end, and a temporary querydef to send the
SQL to a stored
procedure as a pass-through query.

I need to limit each set of data to a certain number of rows (say 5
rows) of data.

I have tried two methods of limiting the data:

1) SET ROWCOUNT 5 in the stored procedure, followed by SET ROWCOUNT 0 at
the end of the procedure.
2) Setting the .MaxRecords property of the temporary querydef.

Unfortunately, both have the same effect:

Even though I expect that 5 rows (maximum) will be returned each time
records are requested, in fact 5 rows IN TOTAL are returned for all
requests.

Thus if the first data set comprises 2 rows, the second will only return
3.

I am closing and recreating the querydef and recordsets between calls to
the stored procedure.

Can anyone tell me why this is happening and how I can get around it?

Thanks

Martin Fine