32-bit Excel and 64-bit SQL Server
Read a plate from Excel in SQL Server ... Actually, what could be easier? There are many possibilities for this. There is an Integration Services tool, which is the former DTS, there is an import / export wizard that is the same “behind the scenes”, you can quickly build your own ADO.NET application, and finally, if you are reluctant to shoot cannons at sparrows, you can use the link mechanism servers, known as DTS, since the time of the Seven, which allows you to easily and elegantly see theoretically any ODBC / OLE DB-reachable source in the form of a table (set of tables) or the result of a direct (ad hoc) query. That was until the 64-bit architecture ceased to be something from the hi end area and came to the laptops of developers and users. Ordinary user, probably after all, it is unlikely that a database server will be installed for itself, but the situation is not exotic for a developer when a 64-bit SQL Server with 32-bit MS Office coexists on a single x64-based machine. In this case, creating a linked server on Excel or Access causes a problem, because the drivers for them are, of course, 32-bit, which SQL Server, being 64-bit, does not understand. He does not have anything similar in the list of providers he knows, although the office with all the gadgets, including connectivity, is on the computer.
Fig. 1
Accordingly, an attempt to use a linked server on Excel, as described in the documentation , leads to the error Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server ...
There is an eternal question "what to do"?
Well, what to do. Put a 64-bit provider, of course. Here ( Microsoft Access Database Engine 2010 Redistributable ) they are just distributed by two pieces: AccessDatabaseEngine.exe and AccessDatabaseEngine_x64.exe. Download the one that is 64-bit, run the installation and get a shit.
Fig.2
That is, if there was no 32-bit Office near the 64-bit SQL Server, then for the sake of Gd, but high technologies have developed to such a height that it is not clear what to do. To demolish 32-bit Office and put 64-bit instead, just to get SQL Server into an Excel tablet? To demolish 64-bit SQL Server and replace 32-bit only for the same? I do not think that this is a flexible solution, and it is not always possible to have such an opportunity. For example, as a man writes ,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. Misunderstanding begins. 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 (see Figure 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.
Actually, the problem is to force the 64-bit version of Microsoft ACE OLEDB Provider to be installed next to the 32-bit office. Why this cannot be done in the forehead is a philosophical question, and a lot of copies are also broken on its subject .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.
As a workaround for myself, I used the installation of AccessDatabaseEngine_X64.exe with the / passive switch, which allows the 64-bit version of the provider to stand side by side with the 32-bit version of Office. It must be emphasized that this method is not officially supported by Microsoft, there are also warnings about possible problems with incompatibility. 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. In my case (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1) there have not been any visible problems (pah-pah-pah), but once again I want to note that this is all that is called, as is and without which any obligations.
I download AccessDatabaseEngine_x64.exe, create a System Restore Point for every fireman (although, in my opinion, when updating office components, it is created automatically anyway), I put it from the command line. Here are the possible keys: Fig.
3
Fig. 4
In contrast to Fig. 2, the installation starts in
Fig. 5
and after about half a minute it is dry, in a business-like manner without any fanfare, OK, cheers, I got up, etc. Now we will see what Sukhov it is. In Fig. 1 from the context menu of the Providers we say Refresh and, as they say, we feel the difference.
Fig.6
It inspires. An unpretentious xslx bungled quickly:
Fig . 7
In SQL Server, create a linked server for this Excel:
Script 1
and fading, read:
Script 2
Goal? No, figs!
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".
No, (Excel forgot to close) still a goal!
Fig. 8
Similarly,
but for her (just in case, I remind you) you must first do
Script 3
In conclusion, it should be noted that the 64-bit provider did not stand side by side with the 32-bit one, but replaced it with itself, so now, for example, I will refuse to work with the Integration Services office in the SQL Server Data Tools for BI project (formerly .BIDS). If you create a connection in the Data Flow of the DTS package on the same Sample.xslsx, an error occurs in the initialization of the Microsoft Office 12.0 Access Database Engine OLE DB Provider, which is logical because devenv.exe is 32-bit and the provider is now 64-bit. To return the behavior, you need to restore the 32-bit office provider ( from there), but then goodbye to the linked server. To make a linked server work, see the beginning of the article. Something like this. My modest needs were currently limited to access to Excel. For example, I have not tested working with an Access database. Provided to readers as an independent exercise. Judging by the information on the connection there is also a place for a feat.
Fig. 1
Accordingly, an attempt to use a linked server on Excel, as described in the documentation , leads to the error Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server ...
There is an eternal question "what to do"?
Well, what to do. Put a 64-bit provider, of course. Here ( Microsoft Access Database Engine 2010 Redistributable ) they are just distributed by two pieces: AccessDatabaseEngine.exe and AccessDatabaseEngine_x64.exe. Download the one that is 64-bit, run the installation and get a shit.
Fig.2
That is, if there was no 32-bit Office near the 64-bit SQL Server, then for the sake of Gd, but high technologies have developed to such a height that it is not clear what to do. To demolish 32-bit Office and put 64-bit instead, just to get SQL Server into an Excel tablet? To demolish 64-bit SQL Server and replace 32-bit only for the same? I do not think that this is a flexible solution, and it is not always possible to have such an opportunity. For example, as a man writes ,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. Misunderstanding begins. 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 (see Figure 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.
Actually, the problem is to force the 64-bit version of Microsoft ACE OLEDB Provider to be installed next to the 32-bit office. Why this cannot be done in the forehead is a philosophical question, and a lot of copies are also broken on its subject .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.
As a workaround for myself, I used the installation of AccessDatabaseEngine_X64.exe with the / passive switch, which allows the 64-bit version of the provider to stand side by side with the 32-bit version of Office. It must be emphasized that this method is not officially supported by Microsoft, there are also warnings about possible problems with incompatibility. 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. In my case (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1) there have not been any visible problems (pah-pah-pah), but once again I want to note that this is all that is called, as is and without which any obligations.
I download AccessDatabaseEngine_x64.exe, create a System Restore Point for every fireman (although, in my opinion, when updating office components, it is created automatically anyway), I put it from the command line. Here are the possible keys: Fig.
3
Fig. 4
In contrast to Fig. 2, the installation starts in
Fig. 5
and after about half a minute it is dry, in a business-like manner without any fanfare, OK, cheers, I got up, etc. Now we will see what Sukhov it is. In Fig. 1 from the context menu of the Providers we say Refresh and, as they say, we feel the difference.
Fig.6
It inspires. An unpretentious xslx bungled quickly:
Fig . 7
In SQL Server, create a linked server for this 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'
Script 1
and fading, read:
select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')
Script 2
Goal? No, figs!
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".
No, (Excel forgot to close) still a goal!
Fig. 8
Similarly,
select * from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Sample.xlsx;HDR=Yes', 'Select * from [Sheet1$]')
but for her (just in case, I remind you) you must first do
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure
Script 3
In conclusion, it should be noted that the 64-bit provider did not stand side by side with the 32-bit one, but replaced it with itself, so now, for example, I will refuse to work with the Integration Services office in the SQL Server Data Tools for BI project (formerly .BIDS). If you create a connection in the Data Flow of the DTS package on the same Sample.xslsx, an error occurs in the initialization of the Microsoft Office 12.0 Access Database Engine OLE DB Provider, which is logical because devenv.exe is 32-bit and the provider is now 64-bit. To return the behavior, you need to restore the 32-bit office provider ( from there), but then goodbye to the linked server. To make a linked server work, see the beginning of the article. Something like this. My modest needs were currently limited to access to Excel. For example, I have not tested working with an Access database. Provided to readers as an independent exercise. Judging by the information on the connection there is also a place for a feat.