?
Solved

setting the DBPROP_MSDAORA_DETERMINEKEYCOLUMNS property of the MS OLEDB provider to False

Posted on 2004-10-23
6
Medium Priority
?
365 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
Comment
Question by:chandra_shailesh
4 Comments
 
LVL 5

Expert Comment

by:jayeshshah
ID: 12395038
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
ID: 12397638
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
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


0
 

Accepted Solution

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

modulo
Community Support Moderator
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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
Course of the Month7 days, 11 hours left to enroll

607 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