[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


setting the DBPROP_MSDAORA_DETERMINEKEYCOLUMNS property of the MS OLEDB provider to False

Posted on 2004-10-23
Medium Priority
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?
Question by:chandra_shailesh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12395038

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;"



Author Comment

ID: 12397638

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.


Expert Comment

ID: 12426330
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


Accepted Solution

modulo earned 0 total points
ID: 13001979
PAQed with no points refunded (of 500)

Community Support Moderator

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

650 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