Заставьте SET IDENTITY_INSERT быстрее действовать из MS Access

Я работаю над повышением размера набора баз данных MS Access до SQL Server. Я написал сценарий SQL для создания схем таблиц в SQL Server. Сейчас я пытаюсь заполнить таблицы. Большинство таблиц имеют первичные ключи автонумерации. Вот мой общий подход:

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

Первая вставка происходит немедленно. Последующие попытки вставки завершаются неудачно с ошибкой: "Невозможно вставить явное значение для столбца идентификаторов в таблице" TblName ", когда для IDENTITY_INSERT задано значение OFF".

Я добавил оператор Resume для этой конкретной ошибки, а также таймер. Оказывается, ошибка продолжается ровно 600 секунд (десять минут), а затем вставка продолжается успешно.

MS Access автоматически обновляет свои сеансы ODBC каждые 10 минут? Есть ли способ заставить это произойти быстрее? Я что-то упускаю из виду?

Справочная информация для тех, кто сразу захочет сказать "Используйте мастер увеличения":
Я не использую встроенный мастер увеличения размера, потому что мне нужно иметь возможность писать сценарий всей операции от начала до конца. Цель состоит в том, чтобы запустить его в тестовой среде перед выполнением коммутатора в расположении клиента.

Источник

3 ответа

Решение

Я нашел ответ на свой первый вопрос. Десять минут - это параметр, скрытый в реестре под ключом Jet engine:

'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).

Этот ключ был установлен по умолчанию 600. Это 600 секунд или 10 минут. Я уменьшил это до десяти секунд, и код ускорился соответственно.

Это ни в коем случае не является полным решением, потому что установка этого значения по умолчанию обязательно вызовет проблемы в другом месте. На самом деле Тони Тоуэс однажды порекомендовал увеличить значение по умолчанию при использовании подключений без DSN.

Я все еще надеюсь найти ответ на вторую часть моего вопроса, а именно, есть ли способ заставить обновление произойти быстрее.

Источник

Две мысли, хотя и не уверен, что будут полезны, потому что это незнакомая для меня территория.

"MS Access автоматически обновляет свои сеансы ODBC каждые 10 минут? Есть ли способ заставить это произойти быстрее? Я что-то упускаю из виду?"

В диалоговом окне "Параметры Access 2003" на вкладке "Дополнительно" есть параметр "Интервал обновления ODBC", а также параметры для повторных попыток. Помогают ли эти настройки... или имеют какой-либо эффект?

Интересно, могли бы вы избежать этой проблемы, создавая столбцы SQL Server в виде простых чисел, а не в виде числа, ВСТАВЛЯЙТЕ свои данные, затем ALTER TABLE ... ALTER COLUMN, чтобы изменить их после вставки данных.

Доступ не позволит мне преобразовать числовой столбец в число, если таблица содержит данные, но ISTR SQL Server более гибок в этом отношении.

Источник

Я нашел удобное, но не очень красивое решение для экспорта многих таблиц доступа на сервер sql и избегания проблемы identity_insert:

  1. Я открываю локальный набор записей таблицы, в котором перечислены все таблицы для экспорта, и я перебираю записи (каждую таблицу). В каждом цикле я...

  2. создать объект приложения доступа

  3. использовать метод передачи базы данных для объекта приложения

  4. завершить/выйти из объекта приложения и снова зациклиться

Вот пример кода:

      Public Sub exporttables()
Dim rst As Recordset
Dim access_object

'First create a local access table which lists all tables to be exported'
Set rst = CurrentDb.OpenRecordset("Select txt_tbl from ####your_table_of_tables####")

 With rst
 While Not .EOF
    'generate a new object to avoid identity insert problem'
    Set access_object = CreateObject("Access.Application")
    'with access object open the database which holds the tables to be exported'
    access_object.OpenCurrentDatabase "####C:\yoursourceaccessdb####.accdb"
    access_object.DoCmd.TransferDatabase acExport, "ODBC Database", "ODBC;DSN=####your connection string to target SQL DB;", acTable, .Fields("txt_tbl"), .Fields("txt_tbl"), False, False
    Debug.Print .Fields("txt_tbl") & " exported"
    access_object.CloseCurrentDatabase
    access_object.Application.Quit
    Set access_object = Nothing
    .MoveNext
Wend
End With

Set rst = Nothing
End Sub
Источник
Другие вопросы по тегам