强制SET IDENTITY_INSERT从MS Access更快生效 [英] Force SET IDENTITY_INSERT to take effect faster from MS Access

问题描述

我正在努力将一组MS Access后端数据库升级到SQL Server.我编写了SQL脚本，以在SQL Server中创建表架构.现在，我试图填充表格.大多数表具有自动编号主键.这是我的一般方法:

I'm working on upsizing a suite of MS Access backend databases to SQL Server. I've scripted the SQL to create the table schemas in SQL Server. Now I am trying to populate the tables. Most of the tables have autonumber primary keys. Here's my general approach:

For each TblName in LinkedTableNames
    'Create linked table "temp_From" that links to the existing mdb'
    'Create linked table "temp_To" that links to the new SQL server table
    ExecutePassThru "SET IDENTITY_INSERT " & TblName & " ON"
    db.Execute "INSERT INTO temp_To SELECT * FROM temp_From", dbFailOnError
    ExecutePassThru "SET IDENTITY_INSERT " & TblName & " OFF"
Next TblName

第一次插入会立即发生.后续的插入尝试失败，并显示以下错误:当IDENTITY_INSERT设置为OFF时，无法在表'TblName'中为标识列插入显式值."

The first insert happens immediately. Subsequent insert attempts fail with the error: "Cannot insert explicit value for identity column in table 'TblName' when IDENTITY_INSERT is set to OFF."

我为该特定错误添加了一个Resume语句，还添加了一个计时器.事实证明，错误会持续准确地持续600秒(十分钟)，然后插入才能成功进行.

I added a Resume statement for that specific error and also a timer. It turns out that the error continues for exactly 600 seconds (ten minutes) and then the insert proceeds successfully.

MS Access是否每隔10分钟自动刷新其ODBC会话?有办法强迫这种情况更快发生吗?我遗漏了明显的东西吗?

Does MS Access automatically refresh its ODBC sessions every 10 minutes? Is there a way to force that to happen faster? Am I missing something obvious?

那些立即想要说使用升迁向导"的人的背景信息:
我没有使用内置的升迁向导，因为我需要能够从头到尾编写整个操作脚本.目的是在客户端位置执行切换之前，先在测试环境中运行此程序.

Background info for those who will immediately want to say "Use the Upsizing Wizard":
I'm not using the built-in upsizing wizard because I need to be able to script the whole operation from start to finish. The goal is to get this running in a test environment before executing the switch at the client location.

推荐答案

我找到了第一个问题的答案.十分钟是Jet引擎项下埋在注册表中的设置:

I found an answer to my first question. The ten minutes is a setting buried in the registry under the Jet engine key:

'Jet WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

'Jet Win7 64-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

'ACE WinXP/ Win7 32-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access Connectivity Engine\Engines\ODBC\ConnectionTimeout

'ACE Win7 64-bit:'
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MicrosoftAccess Connectivity Engine\Engines\ODBC\ConnectionTimeout

它是在此处记录的ACE :

ConnectionTimeout :在超时之前，缓存的连接可以保持空闲的秒数.默认值为600(值的类型为REG_DWORD).

ConnectionTimeout: The number of seconds a cached connection can remain idle before timing out. The default is 600 (values are of type REG_DWORD).

此键设置为默认值600.即600秒或10分钟.我将其减少到十秒钟，代码相应地加快了速度.

This key was set to the default of 600. That's 600 seconds or 10 minutes. I reduced that to ten seconds and the code sped up accordingly.

这绝不是完整的解决方案，因为将默认值设置为低肯定会导致其他问题.实际上， Tony Toews曾经建议，最好将默认设置为使用无DSN的连接时增加.

This is by no means the full solution, because setting the default that low is sure to cause issues elsewhere. In fact, Tony Toews once recommended that the default might better be increased when using DSN-less connections.

我仍然希望找到问题第二部分的答案，即是否有一种方法可以强制刷新速度更快.

I'm still hoping to find an answer to the second part of my question, namely, is there a way to force the refresh to happen faster.

更新:甚至有必要这样做的原因是，链接表使用的会话不同于ADO传递查询.我使用SQL事件探查器进行了测试.以下是一些简短的结果:

UPDATE: The reason this is even necessary is that the linked tables use a different session than ADO pass-through queries. I ran a test using SQL Profiler. Here are some brief results:

TextData                               SPID
-------------------------------------------
SET IDENTITY_INSERT dbo.TblName ON       50
SET IDENTITY_INSERT "dbo"."TblName" ON   49
exec sp_executesql N'INSERT INTO "d...   49
SET IDENTITY_INSERT dbo.TblName OFF      50
SET IDENTITY_INSERT dbo.NextTbl ON       50
SET IDENTITY_INSERT "dbo"."NextTbl" ON   49
exec sp_executesql N'INSERT INTO "d...   49

这里发生的是我的ADO命令在与链接表(#50)不同的会话(#49)中运行. Access看到我正在设置标识列的值，因此可以为该表设置IDENTITY_INSERT ON.但是，它永远不会将IDENTITY_INSERT设置为OFF .我手动将其关闭，但这是在另一个会话中发生的.

What's going on here is that my ADO commands are running in a different session (#49) than my linked tables (#50). Access sees that I'm setting the value for an identity column so it helpfully sets IDENTITY_INSERT ON for that table. However, it never sets IDENTITY_INSERT OFF. I turn it off manually, but that's happening in a different session.

这说明了为什么将ODBC会话超时设置为低的原因.对于Access永远不会关闭表上的IDENTITY_INSERT的事实，这只是一个丑陋的解决方法.由于IDENTITY_INSERT是特定于会话的，因此创建新会话就像单击IDENTITY_INSERT上的重置按钮一样.然后Access可以为下一张表打开它，并且该设置将生效，因为它是一个全新的会话.

This explains why setting the ODBC session timeout low works. It's just an ugly workaround for the fact that Access never turns off IDENTITY_INSERT on a table once it turns it on. Since IDENTITY_INSERT is sessions-specific, creating a new session is like hitting the reset button on IDENTITY_INSERT. Access can then turn it on for the next table and the setting will take effect because it's a brand new session.

这篇关于强制SET IDENTITY_INSERT从MS Access更快生效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！