Stored Procedure row limit in MII

[raj44]

Hi,

We've a transaction that reads data from an Excel sheet and inserts those records in DB by calling a procedure. The application works fine if the rows in Excel is limited to 49. However, if the no. of rows exceed 49, then it doesn't work. On calling the procedure in MII with more than 49 rows of data, MII only returns the first 49 rows.

We have set the row count in MII to 10000.

Any inputs?

Regards

Rajeev

[raj44 · Accepted solution]

All, Thanks for your response. Turns out it was a Datasource issue, on refreshing the data source query returned all the records.

Thanks

[jcgood25 · Accepted solution]

Artificially increasing RowCount isn't the answer, especially if aspects in the code need to preallocate memory for resultsets, etc.  In a sense this is why all queries contain the RowCount parameter - to not have unbounded requests (if I'm not mistaken there is also a safety cap override in the code).  Once you've issued an infinite/unbound query to a database, closing the browser will not stop this from continuing to process.

If you are using something like an SQL stored procedure, then issuing SET ROWCOUNT 0 at the beginning of the procedure will allow sub selects and joins to not fall subject to the RowCount of the query, which like was mentioned, is there more appropriately for the returned resultset.  Of course - this should be used with discretion.

[swaroop_anasane]

Hi Rajeev,

What happens when you call the sp directly from sql while excel has more than 49 rows?

Regards,

Swaroop

[raj44]

No problems in sql

[Former Member]

Hi Rajeev,

How are you reading the Excel? eg via PCo, as XML file, direct connection from db to excel etc.

How are you inserting the rows?

Whats in the procedure, the insert routing or a select? and Can it be run directly an sql developer application?

The row count doesn't affect the insert as there is no resultset. It limits only the number of rows selected from the query.

Regards

Maaz

[raj44]

The excel file is ready via MII transaction and then the response from the transaction is sent to SP to insert data in db.