PRB: GetNextRows Returns DB_S_ROWLIMITEXCEEDED When DBPROP_MAXOPENROWS Is 0 (294133)

MORE INFORMATION

IRowset::GetNextRows returns DB_S_ROWLIMITEXCEEDED when fetching the number of requested rows would have exceeded the total number of active rows supported by the rowset, as determined by the value of the DBPROP_MAXOPENROWS rowset property. For the providers listed at the beginning of this article, DBPROP_MAXOPENROWS is 0, so you would not expect the number of active rows in the rowset to be limited.

However, due to resource restrictions internal to an OLE DB provider, a provider may not be able to provide the number of rows you request, even if DBPROP_MAXOPENROWS is 0. If you request a large number of rows, you should always check for DB_S_ROWLIMITEXCEEDED to make sure that all of your rows are fetched as expected.

Note that this error may occur with other providers, but it may be because the DBPROP_MAXOPENROWS is actually set to something other than 0 and you have requested more rows than this maximum value. In this case, it is expected that GetNextRows will return DB_S_ROWLIMITEXCEEDED. This is typically the case when using the Microsoft Jet 4.0 OLE DB Provider, which sets DBPROP_MAXOPENROWS to 1 by default.

Steps to Reproduce Behavior

1. Use the following SQL statement to create a test table:

   CREATE TABLE tblManyRows (col1 CHAR(255) NOT NULL)
   					

2. Add 5000 rows to the table by repeatedly executing the following INSERT statement:

   INSERT INTO tblManyRows VALUES('Test')
   					

3. Run the following code in a new Microsoft Visual C++ console application:
   
   Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   // RowsExceeded.cpp : Defines the entry point for the console application.
   // 
   #include <atldbcli.h>
   #include <iostream>
   using namespace std;
   
   int main(int argc, char* argv[])
   {
      CDataSource DataSource;
      CSession Session;
      CCommand<CDynamicAccessor, CBulkRowset> cmdTest;
      HRESULT hr;
   
      // For GetNextRows call.
      ULONG   m_nCurrentRows=0;
      HROW    *m_phRow=0;
   
      hr = CoInitialize(NULL);
      if (!SUCCEEDED(hr))
      {
             cout << "Could not initialize COM" << endl;
             return 0;
      }
   
      hr = DataSource.OpenFromInitializationString(
             L"Provider=SQLOLEDB.1;Persist Security Info=False;"
             L"User ID=<user name>;Password=<strong password>;Initial Catalog=pubs;"
             L"Data Source=MyServer"
             );
      if (!SUCCEEDED(hr))
      {
             cout << "Could not connect to SQL Server" << endl;
      }
   
      hr = Session.Open(DataSource);
      if (!SUCCEEDED(hr))
      {
             cout << "Could not create session" << endl;
      }
   
      cmdTest.SetRows(0);	// Set 0 rows to fetch.
   	
      hr = cmdTest.Open(Session,"SELECT * FROM tblManyRows");
      if (!SUCCEEDED(hr))
      {
             cout << "Could not open rowset" << endl;
             return 0;
      }
   	
      hr = cmdTest.m_spRowset->GetNextRows(NULL, 0, 5000, &m_nCurrentRows, &m_phRow);
      if (hr != S_OK)
      {
   	   // Note that the hr output printed below would be 265920 which is equivalent to 
   	   // 0x00040EC0. The ID defined for this hr is DB_S_ROWLIMITEXCEEDED which means: 
   	   // "Fetching requested number of rows would have exceeded total number of active 
   	   //  rows supported by the rowset"
   	   cout << "HRESULT coming back from fetching first 5000 rows: " << hr << endl;
      }
   
      cmdTest.Close();
      cmdTest.ReleaseCommand();
   
      return 0;
   }
   					

4. You can also check the HRESULT returned from the GetNextRows call in the output window when running under the debugger.