ADODB查询超时 [英] ADODB query timeout

问题描述

我正在尝试打开一个查询，该查询正在超时.我尝试设置timeout属性，但似乎不想接受它.

I am trying to open a query, which is timing out. I have tried setting the timeout property, but it doesn't seem to want to accept it.

使用MS-SQL Server管理窗口(SQL Server 2005)执行查询需要34秒，所以我知道我需要增加超时时间.

The query takes 34 seconds to execute using MS-SQL Server Management window (SQL Server 2005), so I know I need to increase the timeout.

当前代码:

Public Function retRecordSet(StrSQL)
Dim cmd ' as new ADODB.Command
Dim rs 'As New ADODB.Recordset

Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = StrSQL
cmd.CommandTimeout = 0
Set rs = cmd.Execute

Set retRecordSet = rs
End Function

我也尝试过设置连接本身的超时时间CurrentProject.Connection.CommandTimeout = 120，但是如果我在此命令后立即查询该值，则该值仍为30

I have also tried setting the timeout of the connection itself CurrentProject.Connection.CommandTimeout = 120, but if I query the value right after this command, it remains at 30

连接属性:

Provider = Microsoft.Access.OLEDB.10.0;持久安全信息= False;数据源= MyServer;集成安全性= SSPI;初始目录= MyDatabase;数据提供程序= SQLOLEDB.1

Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Provider=SQLOLEDB.1

Data Source Object Threading Model = 1
Multiple Results = 3
Multiple Parameter Sets = False
SQL Support = 283
Catalog Location = 1
Catalog Term = database
Catalog Usage = 15
Rowset Conversions on Command = True
Extended Properties = 
Cache Authentication = True
Encrypt Password = 
Persist Encrypted = 
Persist Security Info = False
Asynchronous Processing = 0
Connect Timeout = 600
Protection Level = 
Prompt = 4
Mode = 
Location = 
Locale Identifier = 1033
Impersonation Level = 
Window Handle = 
Data Source = MyServer
User ID = 
Password = 
Integrated Security = SSPI
Mask Password = 
Initial Catalog = MyDatabase
Lock Owner = 
Bind Flags = 
General Timeout = 0
Data Provider = SQLOLEDB.1
Autocommit Isolation Levels = 4096
Unique Reshape Names = False

推荐答案

不确定您是否已解决问题，但我遇到了同样的问题. 我正在用Recordset.Open SQL_String，连接.

Not sure if you already got over the problem but I had the same issue. I'm doing it with Recordset.Open SQL_String, Connection.

在此之前，我只是设置超时属性，而不是在Recordset或Command上，而是在Connection对象上设置:

And before that I just set the timeout property, not on the Recordset or Command but on the Connection object:

Connection.CommandTimeout = 0

这篇关于ADODB查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！