DOCUMENT:Q236933  23-AUG-2001  [visualc]
TITLE   :PRB: DB_E_ERRORSOCCURRED returned from Open When using GROUP BY
PRODUCT :Microsoft C Compiler
PROD/VER::6.0
OPER/SYS:
KEYWORDS:kbDatabase kbDTL kbOLEDB kbSQLServ kbVC600 kbATL300 kbGrpDSVCDB kbOLEDB260kbfaq

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual C++, 32-bit Enterprise Edition, version 6.0 
-------------------------------------------------------------------------------

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 get a DB_E_ERRORSOCCURRED
error from 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 the SQL
Server 7.0 Books Online.

MORE INFORMATION
================

Steps to Reproduce the Behavior
-------------------------------

1. Run the following script on SQL Server:

        Create table myProducts ( ProductName NVarchar (40),  UnitsOnOrder smallint)
        go
        insert into myProducts values ( 'A', 1)
        go

2. Use the ATL OLE DB Consumer Wizard to create a CCommand class, which will be
   used to open up a rowset on the table. Mark it for change, insert, and
   delete. Change the SQL command in the wizard-generated code to the
   following:

   	DEFINE_COMMAND(CdbomyProductsAccessor, _T(" \ 
   	SELECT \ 
   		ProductName, \ 
   		MAX(UnitsOnOrder)  \ 
   		FROM dbo.myProducts GROUP BY ProductName"))

3. Using the new CCommand derived class, do the following:

   	HRESULT hr;
   	CdbomyProducts rs1;
   	hr= rs1.Open();

   You will notice that DB_E_ERRORSOCCURRED is returned from the Open() call.

Additional query words:

======================================================================
Keywords          : kbDatabase kbDTL kbOLEDB kbSQLServ kbVC600 kbATL300 kbGrpDSVCDB kbOLEDB260 kbfaq
Technology        : kbVCsearch kbAudDeveloper kbVC600 kbVC32bitSearch
Version           : :6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.