Solved

setting the DBPROP_MSDAORA_DETERMINEKEYCOLUMNS property of the MS OLEDB provider to False

Posted on 2004-10-23
313 Views
Last Modified: 2013-12-25
The program is using MS OLEDB provider and there is a query

select count(*) from table where column1 is null;

This query gives the following error "ORA-00937: not a single-group group function". However this works fine with SQL Plus.

I came to know that if DBPROP_MSDAORA_DETERMINEKEYCOLUMNS parameter of MS OLEDB  is set to True, then the pseudo column "rowid" is appending to the select list....  so while executing the query becomes

select count(*).rowid from table where column1 is null;

Hence the error. Now I need to set the parameter
DBPROP_MSDAORA_DETERMINEKEYCOLUMNS  to false. How can I do the same?
0
Question by:chandra_shailesh
    4 Comments
     
    LVL 5

    Expert Comment

    by:jayeshshah
    Hi,

    you can do this in your connectionstring.

    please see below an example
    Con.open "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;DBPROP_MSDAORA_DETERMINEKEYCOLUMNS=false;"


    Regards

    JBS
    0
     

    Author Comment

    by:chandra_shailesh
    Hi JBS,

    Thanks for the response. I tried setting both the DBPROP_MSDAORA_DETERMINEKEYCOLUMNS and DBPROP_UNIQUROWS to false. But the oracle error "ORA-00937" still persisted. Then I tried changing the cursor type from ClientSide to ServerSide and this seems to work. Now I have a connection string which looks like  

    Con.open "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;Data Source=abc;"

    and while opening the recordset I am using the

    psd_rs.CursorLocation = adUseServer.

    Regards,
    Anoop
    0
     
    LVL 2

    Expert Comment

    by:TylerTheWimp
    Using a server-side recordset is ill-advised for any database app that will require numerous connections presently or in the future.  If this is the case in your situation, I would try using  a client-side recordset and work through the problems you are having.  Perhaps you need to set the dynamic property on the recordset object and not the connection object:


    dim psd_rs as adodb.recordset
    set psd_rs = new adodb.recordset

    set psd_rs.activeconnection = <open oracle connection variable>
    psd_rs.properties(" DBPROP_MSDAORA_DETERMINEKEYCOLUMNS") = false


    0
     

    Accepted Solution

    by:
    PAQed with no points refunded (of 500)

    modulo
    Community Support Moderator
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now