奇怪的ADO行为生成不需要的NO_BROWSETABLE /在VB6中设置fmtonly查询 [英] Strange ADO behavior generating unwanted NO_BROWSETABLE / set fmtonly queries in VB6

问题描述

我正在努力解决ADO自动生成的一些不需要的元数据请求。用SQLOLEDB替换默认的MSDASQL提供程序后，行为开始。我也尝试过SQLOLEDB，而且行为似乎是相同的。

I am strugling with some unwanted metadata requests automatically generated by ADO. The behavior started after replacing the default MSDASQL provider with SQLOLEDB. I have also tried the SQLOLEDB and the behavior seems to be the same.

从IDE再现问题非常困难，因为它似乎是随机发生的。 。更令人困惑的是，这些查询似乎请求先前打开（然后关闭）的记录集的元数据。

Reproducing the problem from the IDE has proved to be difficult, because it seems to be happening kinda random. Whats even more confising is that these queries seems to request metadata for previously opened (and then closed) recordsets.

推荐答案

我入侵了一些与VB6 SP6上使用ADO 2.8的有害NO_BROWSETABLE / fmtonly / 1 = 2查询相关的奇怪行为。

I have invastigated some strange behavior related to the unwanted NO_BROWSETABLE / fmtonly / 1=2 queries using ADO 2.8 in VB6 SP6 on a XP machine.

以下代码将重现此行为。

The following code will reproduce the behavior.

Private Sub Form_Load()
    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection

    'oConn.Open "DRIVER=SQL Server;SERVER=***;UID=***;password=***;APP=***"
    'oConn.Open "Provider=SQLOLEDB;SERVER=***;UID=***;password=***;APP=***"
    'oConn.Open "Provider=SQLNCLI10;Server=***;UID=***;PWD=***;APP=***"

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    ' Execute the first query
    rst.Open "select 1", oConn
    rst.Close

    ' Execute the second query without specifying a connection
    rst.Open "select 2"
End Sub

我已经使用thr测试了上面的代码ee不同的提供者。这是SQL Server Profiler的结果。

I have tested the code above using three different providers. Here are the SQL Server Profiler results.

提供程序1（MSDASQL）

Provider 1 (MSDASQL)

select 1
go
select 2
go

提供者2（SQLOLEDB）

Provider 2 (SQLOLEDB)

select 1
go

-- This comes after executing the second query. Notice the query between FMTONLY ON/OFF is the FIRST query. 
SET NO_BROWSETABLE ON
go
SET FMTONLY ON select 1 SET FMTONLY OFF
go
SET NO_BROWSETABLE OFF
go
select 2
go

提供者3（SQLNCLI10）

Provider 3 (SQLNCLI10)

select 1
go

-- This comes after executing the second query. The behavior is the same as for SQLOLEDB, except that 'where 1=2' is appended to the query.
SET NO_BROWSETABLE ON
go
 set fmtonly on select 1 where 1=2 set fmtonly off
go
SET NO_BROWSETABLE OFF
go
select 2
go

结论：

如果您没有在第二个rst.Open中指定连接，则ADO将在继续之前向服务器请求有关PREVIOUS查询的元数据。

If you don't specify a connection in the second rst.Open then ADO will request the server for metadata regarding the PREVIOUS query before continuing.

rst.Open "select 2", oConn

如上所述指定oConn将消除元数据请求，并且SQL Server Profiler将为所有三个提供程序产生相同的事件。

Specifying oConn as above will eliminate the metadata request and the SQL Server Profiler will yield the same events for all three providers.

这篇关于奇怪的ADO行为生成不需要的NO_BROWSETABLE /在VB6中设置fmtonly查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！