Прочитать в SQL Server табличку из Excel… В самом деле, что может быть проще? Для этого существует масса возможностей. Есть инструмент Integration Services, который бывшие DTS, есть мастер импорта/экспорта, который «за сценой» то же самое, можно по-быстрому сваять собственное ADO.NET-приложение, наконец, если неохота стрелять из пушек по воробьям, можно воспользоваться механизмом прилинкованных серверов, известным, как DTS, еще со времен семерки, который позволяет легко и элегантно увидеть теоретически любой ODBC/OLE DB-достижимый источник в виде таблицы (совокупности таблиц) или результата непосредственного (ad hoc) запроса. Так было до тех пор, пока 64-битная архитектура не перестала быть чем-то из области hi end и пришла на ноутбуки разработчиков и пользователей. Обычный пользователь, наверное, все-таки вряд ли будет ставить себе сервер баз данных, но для разработчика отнюдь не экзотична ситуация, когда на одной х64-машине уживаются 64-битный SQL Server с 32-битным MS Office. В этом случае создание прилинкованного сервера на Excel или Access вызывает проблему, потому что драйвера для них, понятно, 32-битные, которые SQL Server, будучи 64-битным, не понимает. Нет у него в списке известных ему провайдеров ничего похожего, хотя офис со всеми прибамбасами, включая connectivity, на компе стоит.
Рис.1
Соответственно, попытка использовать прилинкованный сервер на Excel, как описано в документации, приводит к ошибке Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server…
Возникает извечный вопрос «что делать»?
Ну как что делать. Поставить 64-битного провайдера, разумеется. Вот здесь (Microsoft Access Database Engine 2010 Redistributable) их как раз раздают две штуки: AccessDatabaseEngine.exe и AccessDatabaseEngine_x64.exe. Скачиваем ту, которая 64-битная, запускаем установку и получаем отлуп.
Рис.2
То есть если бы рядом с 64-битным SQL Server не стоял 32-битный Office, то за ради Б-га, но высокие технологии у нас развились до такой высоты, что непонятно, что делать. Сносить 32-битный Office и ставить вместо него 64-битный только для того, чтобы залезть SQL Serverом в Excelную табличку? Сносить 64-битный SQL Server и ставить взамен 32-битный только тоже для того же? Не думаю, что это гибкий вариант решения, да и не всегда подобная возможность имеет место быть. Например, как пишет мужик, The MS Access database is a production environment at the customers site, and it is not an option for me to de-install it and install a 64 bits version. It is also not an option to de-install the existing SQL Server 64 bit and install the 32 bits version, of course. An additional installation of a SQL Server 32 bits, only providing the link to MS Access would be the only option then. I find this quite horrible. Начинается непонимайка. Since there is no 64-bit Jet, you need download and install the 64-bit version of «Microsoft Access Database Engine 2010 Redistributable». — Thanks for your answer, but I when I install the AccessDatabaseEngine_X64.exe I get the following error (см.Рис.1) — Please refer to this KB article: support.microsoft.com/kb/2269468 — The article in the link in your last message tells to de-install all Office 32 bits products. That makes no sense to me, because the goal is to connect to the 32 bits MS Access database... — You need to install the 64-bit version of “Microsoft ACE OLEDB Provider” to connect to an Access file (32-bit or 64-bit) from SQL Server. — But I tried to install the 64 bits driver, but it is giving me an installation error... — The previous KB article explains the cause and provide the solution, you can refer to it. — You are guiding me in circles.
Собственно, проблема в том, чтобы заставить поставиться 64-битную версию Microsoft ACE OLEDB Provider рядом с 32-битным офисом. Почему этого не удается сделать в лоб — вопрос философский, и на его тему тоже сломано немало копий. The fact that they cannot forces us to compile two different versions of our application; one specifically for x86 platform and one for x64 platform. Then we have to somehow determine which version of the ACE driver is installed so we know which version (32 or 64 bit) of our application to install. Life would be much simplier if we could just compile our app for the default AnyCPU and then both 32 and 64 dataproviders were allowed to be installed… Why can't 32 and 64 bit ACE coexist? — It's based upon the fact that Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010 or their dependent components... — I'm sorry, but that's not a very helpful answer to the question. Q: Why can't the 32 and 64 bit versions of the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010. That's not answering, that's just repeating the question in other words. Q: Why can't I do this? A: Because we don't support it. Q: Why can't you support it? A: Because we don't support it.
В качестве обходного пути решения для себя я использовал установку AccessDatabaseEngine_X64.exe с ключом /passive, который позволяет 64-битной версии провайдера встать бок о бок с 32-битной версией Office. Необходимо подчеркнуть, что этот способ не является официально поддерживаемым Microsoft, имеются также предостережения относительно возможных проблем с несовместимостью. When I installed the driver AccessDatabaseEngine_x64.exe… MS Office Pro Plus starts a MS Configuration Process that can take up to a minute or two before a document or Office program opens. If office 2010 32 bit is installed and if 64-bit ace is installed with /passive, then **every** time you run Access 2010, you get an automatic installation routine that resets to the 32-bit drivers for office. В моем случае (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1) видимых проблем (тьфу-тьфу-тьфу) пока не возникло, однако еще раз хочу заметить, что это все, что называется, as is и без каких-либо обязательств.
Скачиваю AccessDatabaseEngine_x64.exe, создаю System Restore Point на всякий пожарный (хотя, по-моему, при обновлении офисных компонент она и так создается автоматически), ставлюсь из командной строки. Вот возможные ключи:
Рис.3
Рис.4
В отличие от Рис.2 установка запускается
Рис.5
и где-то через полминуты сухо, по-деловому завершается без всяких фанфар, ОК, ура, я встала и т.д. Сейчас мы поглядим, какой это Сухов. На Рис.1 из контекстного меню Providers говорим Refresh и, что называется, чувствуем разницу.
Рис.6
Это вселяет. По-быстрому сварганил незатейливый xslx:
Рис.7
В SQL Server создаем прилинкованный сервер на этот Excel:
Скрипт 1
и с замиранием читаем:
Скрипт 2
Гол? Нет, фиг!
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv» reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv».
Нет, (Excel забыл закрыть) все-таки гол!
Рис.8
Аналогично,
но для нее (на всякий случай напомню) требуется вначале сделать
Скрипт 3
В заключение следует отметить, что 64-битный провайдер не встал бок о бок с 32-битным, а заменил его собой, поэтому теперь, например, у меня откажутся работать с офисом Integration Services в проекте SQL Server Data Tools for BI (бывш.BIDS). Если на тот же Sample.xslsx создавать соединение в Data Flow DTS-пакета, возникнет ошибка инициализации Microsoft Office 12.0 Access Database Engine OLE DB Provider, что логично — поскольку devenv.exe 32-битная, а провайдер теперь 64-битный. Чтобы вернуть поведение, нужно восстановить 32-битный офисный провайдер (оттуда же), но тогда до свидания прилинкованный сервер. Чтобы заработал прилинкованный сервер — см. начало статьи. Как-то так. Мои скромные потребности ограничивались на данный момент доступом в Excel. Например, работу с БД Access я не проверял. Предоставляется читателям в качестве самостоятельного упражнения. Судя по информации на коннекте там есть тоже место для подвига.
Рис.1
Соответственно, попытка использовать прилинкованный сервер на Excel, как описано в документации, приводит к ошибке Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server…
Возникает извечный вопрос «что делать»?
Ну как что делать. Поставить 64-битного провайдера, разумеется. Вот здесь (Microsoft Access Database Engine 2010 Redistributable) их как раз раздают две штуки: AccessDatabaseEngine.exe и AccessDatabaseEngine_x64.exe. Скачиваем ту, которая 64-битная, запускаем установку и получаем отлуп.
Рис.2
То есть если бы рядом с 64-битным SQL Server не стоял 32-битный Office, то за ради Б-га, но высокие технологии у нас развились до такой высоты, что непонятно, что делать. Сносить 32-битный Office и ставить вместо него 64-битный только для того, чтобы залезть SQL Serverом в Excelную табличку? Сносить 64-битный SQL Server и ставить взамен 32-битный только тоже для того же? Не думаю, что это гибкий вариант решения, да и не всегда подобная возможность имеет место быть. Например, как пишет мужик, The MS Access database is a production environment at the customers site, and it is not an option for me to de-install it and install a 64 bits version. It is also not an option to de-install the existing SQL Server 64 bit and install the 32 bits version, of course. An additional installation of a SQL Server 32 bits, only providing the link to MS Access would be the only option then. I find this quite horrible. Начинается непонимайка. Since there is no 64-bit Jet, you need download and install the 64-bit version of «Microsoft Access Database Engine 2010 Redistributable». — Thanks for your answer, but I when I install the AccessDatabaseEngine_X64.exe I get the following error (см.Рис.1) — Please refer to this KB article: support.microsoft.com/kb/2269468 — The article in the link in your last message tells to de-install all Office 32 bits products. That makes no sense to me, because the goal is to connect to the 32 bits MS Access database... — You need to install the 64-bit version of “Microsoft ACE OLEDB Provider” to connect to an Access file (32-bit or 64-bit) from SQL Server. — But I tried to install the 64 bits driver, but it is giving me an installation error... — The previous KB article explains the cause and provide the solution, you can refer to it. — You are guiding me in circles.
Собственно, проблема в том, чтобы заставить поставиться 64-битную версию Microsoft ACE OLEDB Provider рядом с 32-битным офисом. Почему этого не удается сделать в лоб — вопрос философский, и на его тему тоже сломано немало копий. The fact that they cannot forces us to compile two different versions of our application; one specifically for x86 platform and one for x64 platform. Then we have to somehow determine which version of the ACE driver is installed so we know which version (32 or 64 bit) of our application to install. Life would be much simplier if we could just compile our app for the default AnyCPU and then both 32 and 64 dataproviders were allowed to be installed… Why can't 32 and 64 bit ACE coexist? — It's based upon the fact that Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010 or their dependent components... — I'm sorry, but that's not a very helpful answer to the question. Q: Why can't the 32 and 64 bit versions of the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010. That's not answering, that's just repeating the question in other words. Q: Why can't I do this? A: Because we don't support it. Q: Why can't you support it? A: Because we don't support it.
В качестве обходного пути решения для себя я использовал установку AccessDatabaseEngine_X64.exe с ключом /passive, который позволяет 64-битной версии провайдера встать бок о бок с 32-битной версией Office. Необходимо подчеркнуть, что этот способ не является официально поддерживаемым Microsoft, имеются также предостережения относительно возможных проблем с несовместимостью. When I installed the driver AccessDatabaseEngine_x64.exe… MS Office Pro Plus starts a MS Configuration Process that can take up to a minute or two before a document or Office program opens. If office 2010 32 bit is installed and if 64-bit ace is installed with /passive, then **every** time you run Access 2010, you get an automatic installation routine that resets to the 32-bit drivers for office. В моем случае (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1) видимых проблем (тьфу-тьфу-тьфу) пока не возникло, однако еще раз хочу заметить, что это все, что называется, as is и без каких-либо обязательств.
Скачиваю AccessDatabaseEngine_x64.exe, создаю System Restore Point на всякий пожарный (хотя, по-моему, при обновлении офисных компонент она и так создается автоматически), ставлюсь из командной строки. Вот возможные ключи:
Рис.3
Рис.4
В отличие от Рис.2 установка запускается
Рис.5
и где-то через полминуты сухо, по-деловому завершается без всяких фанфар, ОК, ура, я встала и т.д. Сейчас мы поглядим, какой это Сухов. На Рис.1 из контекстного меню Providers говорим Refresh и, что называется, чувствуем разницу.
Рис.6
Это вселяет. По-быстрому сварганил незатейливый xslx:
Рис.7
В SQL Server создаем прилинкованный сервер на этот Excel:
if exists (select 1 from sys.servers where name = 'XlsLnkSrv') exec sp_dropserver @server = 'XlsLnkSrv', @droplogins = 'droplogins'
exec sp_addlinkedserver @server = 'XlsLnkSrv', @srvproduct = 'ACE 12.0', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = 'C:\Temp\Sample.xlsx', @provstr = 'Excel 12.0; HDR=Yes'
Скрипт 1
и с замиранием читаем:
select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')
Скрипт 2
Гол? Нет, фиг!
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv» reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv».
Нет, (Excel забыл закрыть) все-таки гол!
Рис.8
Аналогично,
select * from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Sample.xlsx;HDR=Yes', 'Select * from [Sheet1$]')
но для нее (на всякий случай напомню) требуется вначале сделать
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure
Скрипт 3
В заключение следует отметить, что 64-битный провайдер не встал бок о бок с 32-битным, а заменил его собой, поэтому теперь, например, у меня откажутся работать с офисом Integration Services в проекте SQL Server Data Tools for BI (бывш.BIDS). Если на тот же Sample.xslsx создавать соединение в Data Flow DTS-пакета, возникнет ошибка инициализации Microsoft Office 12.0 Access Database Engine OLE DB Provider, что логично — поскольку devenv.exe 32-битная, а провайдер теперь 64-битный. Чтобы вернуть поведение, нужно восстановить 32-битный офисный провайдер (оттуда же), но тогда до свидания прилинкованный сервер. Чтобы заработал прилинкованный сервер — см. начало статьи. Как-то так. Мои скромные потребности ограничивались на данный момент доступом в Excel. Например, работу с БД Access я не проверял. Предоставляется читателям в качестве самостоятельного упражнения. Судя по информации на коннекте там есть тоже место для подвига.