It appears that when I specify the MaxRecords property in ADO to something other than 0, not only does SQL Server 2000 limit the number of rows that are being returned from a stored procedure, it also limits the number of "select into"s within the stored proecudre (I'm inserting into table variables within the proc).
Here's a scenario of why this is a problem. Suppose I have a proc that needs to perform calculations on all employees in my system (10,000 of them). The calculation is performed by selecting these employees into a table variable with some calculated values being inserted as well. When the processing is complete, I need to select the top 1500 employees from the table.
If I specify MaxRecords in ADO, the "select into" statement that populates my table variable appears to limit the number of rows to 1500. This is a problem if I'm expecting an employee outside of the first 1500 to be considered for the calculations I'm performing.
Is there some way of indicating that MaxRecords should be ignored when selecting into table variables or temporary tables within a stored proc?
I know I could simply use "top" in the last select statement of the stored procedure to get around this problem, however, this is not an option on the system I'm currently developing (for design reasons (possibly poor design reasons now that I think about it ))