SQL-Ex blog

Средний ответ: Да, но связанные серверы в SQL Server к Access и Excel поддерживаются только при использовании устаревшего 32-разрядного провайдера Microsoft.JET.OLEDB.4.0 OLE DB.

Длинный ответ: Да, но это должна быть часть любого автоматизированного процесса ETL. Пожалуйста, разрабатывайте использование обмена файлами Excel в любых автоматизированных процессах получения данных. Тем не менее...

Множество контента в интернете относительно связанных серверов зависит от старых файлов .xls, и я не буду воссоздавать это здесь. Тем не менее, файлы .xlsx (с использованием последней версии Office 365) и распространяемые драйверы Microsoft Access Database Engine 2016 (ACE12) также работают. Они просто не поддерживаются Microsoft. Связанные серверы с Access и Excel поддерживаются только при использовании 32-битного провайдера Microsoft.Jet.OLEDB.4.0 (несмотря на то, что он устарел).

Вот доказательство:




Пример кода:

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'BOOK112', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:Program FilesMicrosoft SQL ServerMSSQL15.SQL2K19MSSQLDATABook1.xlsx', @provstr=N'Excel 12.0;IMEX=1;HDR=YES;'

GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;   

GO

Обратите внимание на некоторые вещи в сценарии выше:

1. Требуется включить две системных настройки OLEDB ('DynamicParameters' и 'AllowInProcess').


2. Я разместил файл Excel в C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2K19\MSSQL\DATA просто чтобы избежать проблем с локальными разрешениями. Вы можете поместить его где угодно. Конечно, следует предоставить разрешения учетной записи службы SQL Server - независимо от того, как она сконфигурирована для запуска - к файлу Excel.


3. Используется провайдер Microsoft Office 12.0 Access Database Engine OLE DB. Я не смог заставить это работать с Microsoft Office 16.0 Access Database Engine OLE DB Provider, но я не очень упорно пытался.

Это бывает непросто, поскольку Excel может быть легко заблокирован другими процессами. Если у вас лист открыт в Excel, SQL Server не сможет выполнить запрос к нему из связанного сервера. Убейте процессы, которые могут удерживать блокировку файла Excel, включая сам Microsoft Excel. Если это не поможет, и файл остается блокированным, я также использую sysinternals Process Explorer, чтобы убить dllhost.exe, который по-прежнему связан с файлом, что показано на изображении ниже. Прерывание этого процесса в Process Explorer решает проблему:



Опять таки, из-за этих проблем с блокировкой файла - SQL может блокироваться любым другим пользователем, любым локальным процессом на сервере - использование файлов Excel в системе автоматизации системы ETL/ELT просто напрашивается на неприятности. Очень не рекомендуется. Имеется мириад лучших способов получать данные, чем с помощью Excel, включая обход блокирования файла и проблем с драйвером путем экспорта файлов Excel в формат .scv, или предпочтительным обращением напрямую к источнику данных, минуя Excel в качестве носителя. Наиболее предпочтительно получение данных из системы источника путем непосредственного подключения в базе данных. Если данные обрабатываются или вводятся вручную, рассмотрите возможность использования SharePoint Online или PowerApps в качестве источника входных данных вместо автономных файлов Excel. Кроме того, прежде чем исключить какой-либо старый/унаследованный провайдер базы данных, обратите внимание на PolyBase с использованием generic ODBC для драйверов конкретной платформы, или даже драйверы generic ODBC для извлечения данных из широкого диапазона источников данных.

Помимо прочего, драйвер ACE "не предназначен... для использования системной службой или программой на стороне сервера, когда код должен запускаться под системной учетной записью, или будет иметь дело с идентификацией нескольких пользователей одновременно, или имеет высокую степень реинтерабельности и ожидает поведения без сохранения состояния". Сюда относятся связанные серверы в SQL Server, и означает, что это является непредназначенным использованием движка базы данных Access. Еще раз повторю, что решение, обсуждаемое в данной публикации не поддерживается Microsoft. Я обновлю статью, если это изменится.