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).
编辑：尝试将设计师选项卡中的30秒超时设置为600秒，以确保，但我仍然得到相同的错误（在页面加载btw 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 = 0 to 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帐户，但即使这样也无效，仍然会收到“查询超时已过期”。
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.
没工作。只是尝试将确认设置为假但仍然有一段时间。
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更新同一“数据库”数据库中其他表中的帖子。我还可以在localhost上更新其他数据库中的表。
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用户。
1 个解决方案
#1
10
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.
因此，底线是要小心打开的记录集 - 即使它们是只读的，它们也可以锁定更新的表。
#1
10
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.
因此，底线是要小心打开的记录集 - 即使它们是只读的，它们也可以锁定更新的表。