SQL 如何防止“查询超时过期”？（SQLNCLI11 错误“80040e31”）

How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31')

提问by user3435078

I have a connection to a MS SQL Server 2012 database in classic ASP (VBScript). This is my connection string:

我在经典 ASP (VBScript) 中有一个到 MS SQL Server 2012 数据库的连接。这是我的连接字符串：

Provider=SQL Server Native Client 11.0;Server=localhost;
Database=databank;Uid=myuser;Pwd=mypassword;

When I execute this SQL command:

当我执行此 SQL 命令时：

UPDATE [info] SET [stamp]='2014-03-18 01:00:02',
[data]='12533 characters goes here',
[saved]='2014-03-18 01:00:00',
[confirmed]=0,[ip]=0,[mode]=3,[rebuild]=0,
[updated]=1,[findable]=0 
WHERE [ID]=193246;

I get the following error:

我收到以下错误：

Microsoft SQL Server Native Client 11.0 
error '80040e31'
Query timeout expired   
/functions.asp, line 476

The SQL query is pretty long, the data field is updated with 12533 characters. The ID column is indexed so finding the post with ID 193246 should be fast.

SQL 查询很长，数据字段更新为 12533 个字符。ID 列已编入索引，因此查找 ID 为 193246 的帖子应该很快。

When I execute the exact same SQL expression (copied and pasted) on SQL Server Management Studio it completes successfully in no time. No problem what so ever.So there isn't a problem with the SQL itself. I've even tried using a ADODB.Recordset object and update via that (no self-written SQL) but I still get the same timeout error.

当我在 SQL Server Management Studio 上执行完全相同的 SQL 表达式（复制和粘贴）时，它立即成功完成。没问题。所以SQL本身没有问题。我什至尝试使用 ADODB.Recordset 对象并通过它更新（没有自己编写的 SQL），但我仍然遇到相同的超时错误。

If I go to Tools > Options > Query Execution in the Management Studio I see that execution time-out is set to 0 (infinite). Under Tools > Options > Designers I see that transaction time-out is set to 30 seconds, which should be plenty enough since the script and database is on the same computer ("localhost" is in the connection string).

如果我在 Management Studio 中转到工具 > 选项 > 查询执行，我会看到执行超时设置为 0（无限）。在工具 > 选项 > 设计器下，我看到事务超时设置为 30 秒，这应该足够了，因为脚本和数据库在同一台计算机上（“localhost”在连接字符串中）。

What is going on here? Why can I execute the SQL in the Management Studio but not in my ASP code?

这里发生了什么？为什么我可以在 Management Studio 中执行 SQL 而不能在我的 ASP 代码中执行？

---

---

Edit:Tried setting the 30 sec timeout in the Designers tab to 600 sec just to make sure, but I still get the same error (happens after 30 sec of page loading btw).

编辑：尝试将 Designers 选项卡中的 30 秒超时设置为 600 秒以确保，但我仍然遇到相同的错误（顺便说一句，页面加载 30 秒后发生）。

Here is the code that I use to execute the SQL on the ASP page:

这是我用来在 ASP 页面上执行 SQL 的代码：

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQL Server Native Client 11.0;
Server=localhost;Database=databank;Uid=myuser;Pwd=mypassword;"
Conn.Execute "UPDATE [info] SET [stamp]='2014-03-18 01:00:02',
[data]='12533 characters goes here',[saved]='2014-03-18 01:00:00',
[confirmed]=0,[ip]=0,[mode]=3,[rebuild]=0,[updated]=1,[findable]=0 
WHERE [ID]=193246;"

---

---

Edit 2:Using Conn.CommandTimeout = 0to give infinite execution time for the query does nothing, it just makes the query execute forever. Waited 25 min and it was still executing.

编辑 2：使用Conn.CommandTimeout = 0为查询提供无限执行时间什么都不做，它只会使查询永远执行。等了 25 分钟，它仍在执行。

I then tried to separate the SQL into two SQL statements, the long data update in one and the other updates in the other. It still wouldn't update the long data field, just got timeout.

然后我尝试将 SQL 分成两个 SQL 语句，一个是长数据更新，另一个是更新。它仍然不会更新长数据字段，只是超时。

I tried this with two additional connection strings:

我用两个额外的连接字符串尝试了这个：

Driver={SQL Server};Server=localhost;Database=databank;Uid=myuser;Pwd=mypassword;
Driver={SQL Server Native Client 11.0};Server=localhost;Database=databank;Uid=myuser;Pwd=mypassword;

Didn't work. I even tried changing the data to 12533 A's just to see if the actual data was causing the problem. Nope, same problem.

没用。我什至尝试将数据更改为 12533 A，只是为了查看实际数据是否导致了问题。不行，同样的问题。

Then I found out something interesting: I tried to execute the short SQL first, before the long update of the data field. It ALSO got query timeout exception...

然后我发现了一些有趣的事情：在数据字段的长更新之前，我尝试先执行短SQL。它也有查询超时异常...

But why? It has so little stuff to update in it (the whole SQL statement is less than 200 characters). Will investigate further.

但为什么？它的更新内容太少了（整个 SQL 语句少于 200 个字符）。将进一步调查。

---

---

Edit 3:I thought it might have been something to do with the login but I didn't find anything that looked wrong. I even tried changing the connection string to use the sa-account but even that didn't work, still getting "Query timeout expired".

编辑 3：我认为这可能与登录有关，但我没有发现任何看起来有问题的地方。我什至尝试更改连接字符串以使用 sa-account，但即使这样也不起作用，仍然出现“查询超时已过期”。

This is driving me mad. There is no solution, no workaround and worst of all no ideas!

这让我发疯。没有解决方案，没有解决方法，最糟糕的是没有想法！

---

---

Edit 4:Went to Tools > Options > Designers in the Management Studio and ticked off the "Prevent saving changes that require table re-creation". It did nothing.

编辑 4：转到 Management Studio 中的工具 > 选项 > 设计器并勾选“防止保存需要重新创建表的更改”。它什么也没做。

Tried changing the "data" column data type from "nvarchar(MAX)" to the inferior "ntext" type (I'm getting desperate). It didn't work.

尝试将“数据”列数据类型从“nvarchar(MAX)”更改为劣等的“ntext”类型（我越来越绝望）。它没有用。

Tried executing the smallest change on the post I could think of:

尝试在我能想到的帖子上执行最小的更改：

UPDATE [info] SET [confirmed]=0 WHERE [ID]=193246;

That would set a bit column to false. Didn't work. I tried executing the exact same query in the Management Studio and it worked flawlessly.

这会将位列设置为 false。没用。我尝试在 Management Studio 中执行完全相同的查询，结果完美无缺。

Throw me some ideas if you have got them because I'm running out for real now.

如果你有一些想法，请告诉我，因为我现在真的快用完了。

---

---

Edit 5:Have now also tried the following connection string:

编辑 5：现在还尝试了以下连接字符串：

Provider=SQLOLEDB.1;Password=mypassword;Persist Security Info=True;User ID=myuser;Initial Catalog=databank;Data Source=localhost

Didn't work. Only tried to set confirmed to false but still got a time out.

没用。只尝试将确认设置为 false 但仍然超时。

---

---

Edit 6:Have now attempted to update a different post in the same table:

编辑 6：现在尝试更新同一表中的不同帖子：

UPDATE [info] SET [confirmed]=0 WHERE [ID]=1;

It also gave the timeout error. So now we know it isn't post specific.

它还给出了超时错误。所以现在我们知道它不是特定于帖子的。

I am able to update posts in other tables in the same "databank" database via ASP. I can also update tables in other databases on localhost.

我能够通过 ASP 更新同一“数据库”数据库中其他表中的帖子。我还可以更新本地主机上其他数据库中的表。

Could there be something broken with the [info] table? I used the MS Access wizard to auto move data from Access to MS SQL Server 2012, it created columns of data type "ntext" and I manually went and changed that to "nvarchar(MAX)" since ntext is deprecated. Could something have broken down? It did require me to re-create the table when I changed the data type.

[info] 表是否有问题？我使用 MS Access 向导自动将数据从 Access 移动到 MS SQL Server 2012，它创建了数据类型为“ntext”的列，我手动将其更改为“nvarchar(MAX)”，因为 ntext 已被弃用。会不会有什么东西坏掉了？当我更改数据类型时，它确实需要我重新创建表。

I have to get some sleep but I will be sure to check back tomorrow if anybody has responded to me. Please do, even if you only have something encouraging to say.

我必须睡一觉，但如果有人回复我，我明天一定会回来查看。即使你只是想说些鼓舞人心的话，也请这样做。

---

---

Edit 7:Quick edit before bed. Tried to define the provider as "SQLNCLI11" in the connection string as well (using the DLL name instead of the actual provider name). It makes no difference. Connection is created just as fine but the timeout still happens.

编辑7：睡前快速编辑。还尝试在连接字符串中将提供程序定义为“SQLNCLI11”（使用 DLL 名称而不是实际的提供程序名称）。没有什么不同的。连接创建得一样好，但超时仍然发生。

Also I'm not using MS SQL Server 2012 Express (as far as I know, "Express" wasn't mentioned anywhere during installation). It's the full thing.

此外，我没有使用 MS SQL Server 2012 Express（据我所知，安装过程中没有提到“Express”）。这是完整的东西。

If it helps, here's the "Help" > "About..." info that is given by the Management Studio:

如果有帮助，这里是 Management Studio 提供的“帮助”>“关于...”信息：

Microsoft SQL Server Management Studio: 11.0.2100.60  
Microsoft Analysis Services Client Tools: 11.0.2100.60  
Microsoft Data Access Components (MDAC): 6.3.9600.16384  
Microsoft MSXML: 3.0 5.0 6.0   
Microsoft Internet Explorer: 9.11.9600.16521  
Microsoft .NET Framework: 4.0.30319.34011  
Operating System: 6.3.9600

---

---

Edit 8 (also known as the "programmers never sleep" edit):

编辑 8（也称为“程序员从不睡觉”的编辑）：

After trying some things I eventually tried to close the database connection and reopening it right before executing the SQL statements. It worked all of a sudden. What the...?

在尝试了一些事情之后，我最终尝试在执行 SQL 语句之前关闭数据库连接并重新打开它。它突然起作用了。什么……？

I have had my code inside a subroutine and it turns out that outside of it the post that I was trying to update was already opened! So the reason for the timeout was that the post or the whole table was locked by the very same connection that tried to update it. So the connection (or CPU thread) was waiting for a lock that would never unlock.

我在一个子程序中放置了我的代码，结果在它之外，我试图更新的帖子已经打开了！所以超时的原因是帖子或整个表被试图更新它的同一个连接锁定。所以连接（或 CPU 线程）正在等待一个永远不会解锁的锁。

Hate it when it turns out to be so simple after trying so hard.

当经过如此努力后结果变得如此简单时，讨厌它。

The post had been opened outside the subroutine by this simple code:

该帖子已通过以下简单代码在子程序外打开：

Set RecSet = Conn.Execute("SELECT etc")

I just added the following before calling the subroutine.

我只是在调用子程序之前添加了以下内容。

RecSet.Close
Set RecSet = Nothing

The reason why this never crossed my mind is simply because this was allowed in MS Access but now I have changed to MS SQL Server and it wasn't so kind (or sloppy, rather). The created RecSet by Conn.Execute() had never created a locked post in the database before but now all of a sudden it did. Not too strange since the connection string and the actual database had changed.

我从未想过这件事的原因仅仅是因为这在 MS Access 中是允许的，但现在我已更改为 MS SQL Server 并且它不是那么友好（或马虎）。Conn.Execute() 创建的 RecSet 之前从未在数据库中创建过锁定的帖子，但现在突然之间创建了。不太奇怪，因为连接字符串和实际数据库已更改。

I hope this post saves someone else some headache if you are migrating from MS Access to MS SQL Server. Though I can't imagine there are that many Access users left in the world nowadays.

如果您正在从 MS Access 迁移到 MS SQL Server，我希望这篇文章可以让其他人免于头疼。虽然我无法想象现在世界上还有那么多 Access 用户。

采纳答案by user3435078

Turns out that the post (or rather the whole table) was locked by the very same connection that I tried to update the post with.

结果发现帖子（或者更确切地说是整个表格）被我试图更新帖子的同一个连接锁定了。

I had a opened record set of the post that was created by:

我有一个由以下人员创建的帖子的打开记录集：

Set RecSet = Conn.Execute()

This type of recordset is supposed to be read-only and when I was using MS Access as database it did not lock anything. But apparently this type of record set did lock something on MS SQL Server 2012 because when I added these lines of code before executing the UPDATE SQL statement...

这种类型的记录集应该是只读的，当我使用 MS Access 作为数据库时，它没有锁定任何东西。但显然这种类型的记录集确实在 MS SQL Server 2012 上锁定了某些东西，因为当我在执行 UPDATE SQL 语句之前添加这些代码行时......

RecSet.Close
Set RecSet = Nothing

...everything worked just fine.

...一切正常。

So bottom line is to be careful with opened record sets - even if they are read-only they could lock your table from updates.

所以底线是要小心打开的记录集 - 即使它们是只读的，它们也可以锁定您的表以防止更新。