combination of statements gives "properties not supported" for oledb to sqlserver

Question

combination of statements gives "properties not supported" for oledb to sqlserver

joachim.verhagen

Hall of Fame

Points: 3560
More actions
November 7, 2006 at 2:15 am

#174839

Yesterday a developer came to me with the following problem:
He did the following in a VB program connecting to a SQLServer 2005 SP1 database using the latest MDAC.
He started some transaction.
SELECT kspp.bed_id , kspp.bj , kspp.grb_id , kspp.per_id , ksp.bvd_id , sum( kspp.budget_bedrag ) budget_amount FROM bed , bedj , usrinfo , ksp , ksppWHERE usrinfo.loginname = 'fredg' AND usrinfo.companyID = bed.bed_id AND bedj.bed_id = bed.bed_id AND bedj.k_jaarovergang_grb = 0 AND ksp.bed_id =bed.bed_id AND ksp.bvd_id > 0 AND kspp.bed_id = bed.bed_id AND kspp.bj =bedj.bj AND kspp.kpl_id = ksp.kpl_id AND kspp.grb_id = ksp.grb_id AND bedj.bed_id >= 1 AND bedj.bed_id <= 1 AND bedj.bj >= 2006 AND bedj.bj <= 2006 AND ksp.grb_id >= 1 AND ksp.grb_id <= 99999999999 GROUP BY kspp.bed_id ,kspp.bj , kspp.grb_id , kspp.per_id , ksp.bvd_id
Some processing
SELECT * FROM grbp WHERE bed_id = 1 AND bj = 2006 AND grb_id = 17030 AND budget_bedrag <> 0
This results in :
The requested properties cannot be supported.Error from: 'Microsoft OLE DB Provider for SQL Server' 0x80040E21 The requested properties cannot be supported. Native: 0 (help=.0)
Sometimes the second statement is an UPDATE, which gives the same error.
The SQLServer profiler only shows the first statement. The second statementdoes not arrive.
If the sum term is removed from the first statement the second works without any problem. This was surprising.
In a transaction in SQLServer Management Studio it works fine.
Testing on another machine gives the same result, so I guess it is not a matter of a wrong MDAC installation.
I have no idea what to try next. Can anybody do any suggestions?
Joachim.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

I suspect it has nothing to do with these queries but in the way ADO is used in this VB program. If you go into VB's debug mode what statement is issuing this error? You should write out the full list of errors in the ADO connection Errors collection, e.g.

For intI = 0 To MyConnection.Errors.Count - 1

Debug.Print "SQLState=" & CStr(gadoCn.Errors(intI).SQLState) & " Number=" & CStr(&HFFFF& And gadoCn.Errors(intI).Number) & " Description=" & gadoCn.Errors(intI).Description

Next intI

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 2

Both SQL statements where entered with:

ADODB.RecordSet.Open strSQL, aDBcn.Connection, adOpenForwardOnly, adLockOptimistic, -1

I allready sent you all the error messages.

The program produced the following (self programmed) trace:

06-11-2006 15:01:43 DBAaccess.StartLUW Starting Transaction Level=1
06-11-2006 15:01:43 CDBSGenLib.CurrentUserName fredg
06-11-2006 15:01:46 DBArecordSet.OpenSet strSQL=' SELECT kspp.bed_id, kspp.bj, kspp.grb_id, kspp.per_id, ksp.bvd_id, sum(kspp.budget_bedrag) budget_amount  FROM bed, bedj, usrinfo, ksp, kspp  WHERE usrinfo.loginname = 'fredg' AND usrinfo.companyID = bed.bed_id  AND bedj.bed_id = bed.bed_id  AND bedj.k_jaarovergang_grb = 0  AND ksp.bed_id = bed.bed_id  AND ksp.bvd_id > 0  AND kspp.bed_id = bed.bed_id  AND kspp.bj = bedj.bj  AND kspp.kpl_id = ksp.kpl_id  AND kspp.grb_id = ksp.grb_id  AND bedj.bed_id >= 1 AND bedj.bed_id <= 1 AND bedj.bj >= 2006 AND bedj.bj <= 2006 AND ksp.grb_id >= 1 AND ksp.grb_id <= 99999999999 GROUP BY kspp.bed_id, kspp.bj, kspp.grb_id, kspp.per_id, ksp.bvd_id ' on SQLSERVER:fboex111;Data source=fins myCursorType=0,myLockType=3
06-11-2006 15:01:46 DBArecordSet.OpenSet OK
06-11-2006 15:01:46 EOF.DBArecordSet =False
06-11-2006 15:01:46 DBArecordSet.FieldValue bed_id=1
06-11-2006 15:01:46 DBArecordSet.FieldValue bj=2006
06-11-2006 15:01:46 DBArecordSet.FieldValue grb_id=17030
06-11-2006 15:01:46 DBArecordSet.FieldValue per_id=1
06-11-2006 15:01:46 DBArecordSet.FieldValue bvd_id=1
06-11-2006 15:01:46 DBArecordSet.FieldValue budget_amount=1666
06-11-2006 15:02:03 DBArecordSet.OpenSet strSQL='SELECT * FROM grbp WHERE bed_id=1 AND bj=2006 AND grb_id=17030 AND budget_bedrag <> 0 ' on SQLSERVER:fboex111;Data source=fins myCursorType=0,myLockType=3
06-11-2006 15:02:03 DBArecordSet.OpenSet failed reason #-2147217887 The requested properties cannot be supported.
06-11-2006 15:02:03 DBArecordSet.OpenSet Error from: 'Microsoft OLE DB Provider for SQL Server' 0x80040E21 The requested properties cannot be supported. Native: 0  (help=.0)

In the mean time some other test have been done:

It seems to work fine using ODBC, which is not an option for production, as that is all geared to OLEDB. I difference I just heard is that in ODBC the cursor is static while the above cursor is forward only. Might that be the cause of the problem?

Joachim.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

Why are our windows getting so wide? Is it something you did or is it something I did^

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 4

joachim.verhagen

Hall of Fame

Points: 3560

November 8, 2006 at 3:40 am

#670517

I think it is because the code is preformatted.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

I am currently struggling with a problem that has to do with the use of adOpenStatic vs adOpenForwardOnly under ODBC. Not getting your error message though. Instead when I try to read the rows it tells me 3704peration is not allowed when the object is closed. Very, very weird.

You might play with the idea of using a DSN-less ODBC connection using a connect string like so

"DRIVER={SQL Server};SEVER=YourServerComputerName;DATABASE=YourDatabase;UID=User;PWD=Password"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I haven't seen that error since I did web dev some years back, but then it was because of the cursortype and lock type. There are some combinations that aren't allowed.

Don't know if that's any help.

Maybe change the select * to select <field list> and see if it makes any difference

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

Amazing indeed. My select statement was dirt simple:

select distinct colname from tablename

When I removed 'distinct' the problem went away.

I then tried

select colname from tablename group by colname

the problem pops right back.

When I change

adoRs.Open sql, connection, adOpenStatic, adLockReadOnly

to

adoRs.open sql, connection, adOpenForwardOnly, adLockReadOnly

all works.

Joachim, have you tried the DSN-less connection?

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 8

Replacing * with fieldnames has no effect.

The DSNless connection gives a usefull error message:

DBArecordSet.OpenSet Error from: 'Microsoft OLE DB Provider for ODBC Drivers' 0x80004005 Unspecified error Native: 0 (help=.0)

DBArecordSet.OpenSet Error from: 'Microsoft OLE DB Provider for ODBC Drivers' 0x80004005 Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets. Native: 0 (help=.0)

DBArecordSet.OpenSet failed reason #-2147467259 Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.

Changing the first cursor to static:

rValue = aRecSet.OpenSet(strSQL, mDBcn, adOpenStatic)

works with the ODBC connection.

It does not work with the OLEDB connection.

Joachim.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 9

My only guess is that there is something on the connection that hasn't been closed or released or comitted or whatever. I would try this in a simple program that does just that on a clean connection. As an alternative, before issuing the open method of the recordset object you could close the connection and re-open it.

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 10

Just trying things, both cursors were set to static and that resulted in the following error:

Microsoft OLE DB Provider for SQL Server' 0x80004005 Cannot create new connection because in manual or distributed transaction mode.

Searching for this error got me to knowledge base article 910696 (http://support.microsoft.com/kb/910696)

It says: "However, you cannot create an implicit connection as long as a transaction is open. Therefore, the code will fail, and you will receive the following error message:

Cannot create new connection because in manual or distributed transaction mode.

To resolve this problem, explicitly close the Recordset objects that were created by using the GetCustomers and GetOrders functions."

And as this program is going through the first cursor and doing things of each record that is no option. It seems to be impossible. But with a simpler first statement it works!

Michael Meierruth SSChampion Points: 10051 More actions · Answer 11

Sorry can't seem to see navigate to the second page of this thread. But doing a reply I saw the last post on the second page. Thus are you getting closer to a solution?

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 12

The developer seems to be happy making a ODBC connection for this action. Not really a solution, but a workaround. With Oracle there is no problem.