sql server - Static ADO recordset returns RecordCount -1 when using common table expressions -

sql server - Static ADO recordset returns RecordCount -1 when using common table expressions -

according documentation, recordset.recordcount supposed homecoming actual number of rows when using static recordset (emphasis mine):

the recordcount property homecoming -1 forward-only cursor; the actual count static or keyset cursor; , either -1 or actual count dynamic cursor, depending on info source.

however, apparently untrue when using sqloledb provider , sql statement includes mutual table expression:

const sql1 = "select myfield mytable" const sql2 = "with mycte (select myfield mytable) select myfield mytable" dim cn new adodb.connection cn.open "provider=sqloledb;server=myserver;initial catalog=mydatabase;integrated security=sspi" dim rs new adodb.recordset rs.cursorlocation = aduseserver rs.open sql1, cn, adopenstatic, adlockreadonly, adcmdtext debug.print rs.recordcount ' yields 156 ' rs.close rs.open sql2, cn, adopenstatic, adlockreadonly, adcmdtext debug.print rs.recordcount ' yields -1; should 156 ' rs.close cn.close

is known issue?

i aware can work around issue using client-side instead of (default) server-side cursor, i'd rather not avoid introducing regressions in our legacy code.

in sec case, ado not create static cursor. apparently, ole db provider not back upwards server-side static cursors ctes , changes cursor type adopenforwardonly. forward-only cursors homecoming -1 recordcount.

credit discovering goes dan guzman.

sql-server vba oledb ado