Microsoft KB Archive/236933

= PRB: DB_E_ERRORSOCCURRED returned from Open when using GROUP BY and Aggregate function =

Article ID: 236933

Article Last Modified on 3/19/2004

-

APPLIES TO


 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ .NET 2002 Standard Edition

-



This article was previously published under Q236933





SYMPTOMS
When using the Microsoft SQL Server OLE DB Provider (SQLOLEDB) with OLE DB template consumer classes to open a rowset with a SQL query that contains GROUP BY and aggregate functions such as MAX, you might receive a DB_E_ERRORSOCCURRED error from the Open call.



CAUSE
You have marked the rowset for change, insert, or delete when using the ATL Consumer Wizard or have added properties to make the rowset updateable.



RESOLUTION
Do not mark the rowset for change, insert, or delete when the command contains an aggregate function. You can comment out the following lines in your ATL code: propset.AddProperty(DBPROP_IRowsetChange, true); propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);



STATUS
This behavior is by design. If a SELECT statement contains an aggregate function, such as MAX, a server-side cursor is automatically opened with a scroll option of CUR_INSENSITIVE and a concuropt of CUR_READONLY that is not updateable. For additional information, search for "dbcursoropen" in SQL Server 7.0 Books Online.



Steps to reproduce the behavior
 Create a table in SQL Server. To do this, follow these steps:  Connect to SQL Server with SQL Query Analyzer. Switch to the Northwind database.  Paste the following code in SQL Query Analyzer: Create table myProducts ( ProductName NVarchar (40), UnitsOnOrder smallint) go insert into myProducts values ( 'A', 1) go  Press F5</li></ol> </li> Use the ATL OLE DB Consumer Wizard to create a CCommand class that supports the change, insert and delete operations <ol style="list-style-type: lower-alpha;"> Start Visual C++ 6.0</li> On the File menu, click New. The New dialog box appears.</li> On the Projects tab, click MFC AppWizard(exe).</li> On the Projects tab, type Q236933 in the Project box, and then click OK. The MFC AppWizard appears.</li> Click Dialog based as your application type.</li> Click Finish, and then click OK.</li> On the Insert menu, click New ATL Object, and then click OK. The ATL Object Wizard appears.</li> In the left pane, click Data Access, click the Consumer icon in the right pane, and then click Next. The ATL Object Wizard Properties dialog box appears.</li> Click Select Datasource. The Create New Datalink dialog box appears.</li> Click Microsoft OLE DB Provider for SQL Server, and then click Next. Set your SQL SERVER connection to use the Northwind database, and then click OK.</li> In the Select Database Table dialog box, use the tree control to select the dbo.myProducts table, and then click OK. You return to the ATL Object Wizard Properties dialog box.</li> Click to select the Change, Insert, and Delete check boxes to support the changing, inserting, and deleting of records in the rowset.</li> Click OK.</li></ol> </li> Using the new CCommand derived class, do the following: <ol style="list-style-type: lower-alpha;"> <li>Open the dbomyProducts.h file in the workspace.</li> <li> Locate the following code: DEFINE_COMMAND(CdbomyProductsAccessor, _T(" \   SELECT \        ProductName, \        UnitsOnOrder  \        FROM dbo.myProducts")) </li> <li> Replace it with the following code: DEFINE_COMMAND(CdbomyProductsAccessor, _T(" \   SELECT \        ProductName, \        MAX(UnitsOnOrder)  \        FROM dbo.myProducts GROUP BY ProductName")) </li> <li>Open the Q236933Dlg.cpp file in the workspace</li> <li> Add the following code in the top of windows after the #include "Q236933.h" statement: <li> Locate the following code: BOOL CQ1321321Dlg::OnInitDialog </li> <li> Paste the following code above the OnInitDialog function: HRESULT hr; CdbomyProducts rs1; hr= rs1.Open; // 0x80040e21 is the value of DB_E_ERRORSOCCURRED. if (hr==0x80040E21) MessageBox("Command Failed.\rDB_E_ERRORSOCCURRED",NULL,MB_OK); </li> <li>Press Ctrl+F5 to build and run the application. You receive the following error message:
 * 1) include "dbomyProducts.h" </li>

Command Failed. DB_E_ERRORSOCCURRED

</li></ol> </li></ol>

Keywords: kbatl300 kbdatabase kbprb kbdtl kbfaq kbsqlserv kboledb kbgrpdsvcdb KB236933

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.