32-bit Excel and 64-bit SQL Server
Read the Excel table in SQL Server ... In fact, what could be simpler? There are plenty of opportunities for this. There is an Integration Services tool, which were former DTS, there is an import / export wizard, which is the same behind the scenes, you can quickly build your own ADO.NET application, finally, if you don't feel like shooting sparrows, you can use the linking mechanism servers, known as DTS, from 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 (a set of tables) or the result of an immediate (ad hoc) query. It was so until the 64-bit architecture ceased to be something from the hi end area and came to the laptops of developers and users. A normal user, probably, will hardly be able to set himself a database server, but for a developer, the situation when a 64-bit SQL Server with a 32-bit MS Office is coexisting on one x64-machine is not at all exotic. In this case, creating a linked server in 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 known to him, although the office with all the bells and whistles, including connectivity, is on the computer.
Pic.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 the OLE DB provider for "Microsoft.ACE.OLEDB.12.0" for linked server ...
There is an eternal question "what to do?"
Well, what to do. Put 64-bit provider, of course. Right here ( Microsoft Access Database Engine 2010 Redistributable ) they are distributed among two things: AccessDatabaseEngine.exe and AccessDatabaseEngine_x64.exe. Download the 64-bit one, launch the installation and get the bounce.
')
Pic2
That is, if there was no 32-bit Office next to 64-bit SQL Server, then for the sake of Gd, but we have developed high technologies to such a height that it is not clear what to do. Demolish 32-bit Office and put 64-bit instead, just to get SQL Server into an Excel tablet? Demolish a 64-bit SQL Server and put in return 32-bit only for the same, too? I do not think that this is a flexible solution, and this possibility is not always the case. For example, as the man writes , it’s a bit different. It is not a SQL Server 64 bit and 32 bit version, of course. SQL Server 32 bits, only option for MS Access. I find this quite horrible. Neponimika begins. Since there is no 64-bit Jet, you need to download 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 Office 32 bits products. It makes it possible for you to use the 64-bit version of the Microsoft ACE OLEDB Provider. or 64-bit) from SQL Server. I’m trying to install the 64 bits driver . - You are guiding me in circles.
Actually, the problem is to get the 64-bit version of the Microsoft ACE OLEDB Provider next to the 32-bit office to be delivered. Why this cannot be done in the forehead is a philosophical question, and quite a few copies of it are also broken on his subject. Compose two different versions of our application; x86 platform specifically. If you have a driver, you must be able to find out. If you’ve been able to compile our app for the default, you’ve been able to ... and why 32 and 64 bit ACE coexist? - Microsoft Office 2010 or their dependent components, and Microsoft Office 2010 or their dependent components ... Q: Why can't the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft Office 2010 does not support Microsoft Office 2010. It doesn’t support, but it doesn’t support. 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 AccessDatabaseEngine_X64.exe installation with the / passive key, 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 incompatibility issues. When I installed the driver AccessDatabaseEngine_x64.exe ... If office 2010 32-bit is installed and if 64-bit is installed , then ** every ** time you can access the 32-bit drivers for office . In my case (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1), no visible problems (pah-pah-pah) have yet arisen, but once again I want to note that this is all that is called as is and without -or obligations.
I download AccessDatabaseEngine_x64.exe, create a System Restore Point for every fireman (although, in my opinion, when updating office components, it is automatically created anyway), I put it from the command line. Here are the possible keys:
Pic.3
Pic.4
Unlike Figure 2, the installation starts.
Pic.5
and somewhere in half a minute it is dry, in a businesslike way it ends without any fanfare, OK, hurray, I got up, etc. Now we will see what it Sukhov. In Figure 1, from the Providers context menu, we say Refresh and, as they say, we feel the difference.
Pic.6
It infuses. Quickly bungled simple xslx:
Pic.7
In SQL Server we create a linked server for this Excel:
Script 1
and with a sinking read:
Script 2
Goal? No, FIG!
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider for Microsoft.ACE.OLEDB.12.0 for the linked server XlsLnkSrv reported an error. Did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the OLE DB provider for the data source "Microsoft.ACE.OLEDB.12.0" for the linked server "XlsLnkSrv".
No, (Excel forgot to close) after all a goal!
Fig.8
Similarly
but for her (just in case I remind you) you need to do first
Script 3
In conclusion, it should be noted that the 64-bit provider did not stand side by side with 32-bit, 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. ). If you create a connection to the Data Flow DTS package on the same Sample.xslsx, the Microsoft Office 12.0 Access Database Engine OLE DB Provider will fail to initialize, which is logical - since 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 the same place ), but then bye a linked server. To earn a linked server - see the beginning of the article. Something like this. My modest needs were limited at the moment by access to Excel. For example, I did not check access database operation. Provided to readers as an independent exercise. Judging by the information on the connection there is also a place for achievement.
Pic.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 the OLE DB provider for "Microsoft.ACE.OLEDB.12.0" for linked server ...
There is an eternal question "what to do?"
Well, what to do. Put 64-bit provider, of course. Right here ( Microsoft Access Database Engine 2010 Redistributable ) they are distributed among two things: AccessDatabaseEngine.exe and AccessDatabaseEngine_x64.exe. Download the 64-bit one, launch the installation and get the bounce.
')
Pic2
That is, if there was no 32-bit Office next to 64-bit SQL Server, then for the sake of Gd, but we have developed high technologies to such a height that it is not clear what to do. Demolish 32-bit Office and put 64-bit instead, just to get SQL Server into an Excel tablet? Demolish a 64-bit SQL Server and put in return 32-bit only for the same, too? I do not think that this is a flexible solution, and this possibility is not always the case. For example, as the man writes , it’s a bit different. It is not a SQL Server 64 bit and 32 bit version, of course. SQL Server 32 bits, only option for MS Access. I find this quite horrible. Neponimika begins. Since there is no 64-bit Jet, you need to download 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 Office 32 bits products. It makes it possible for you to use the 64-bit version of the Microsoft ACE OLEDB Provider. or 64-bit) from SQL Server. I’m trying to install the 64 bits driver . - You are guiding me in circles.
Actually, the problem is to get the 64-bit version of the Microsoft ACE OLEDB Provider next to the 32-bit office to be delivered. Why this cannot be done in the forehead is a philosophical question, and quite a few copies of it are also broken on his subject. Compose two different versions of our application; x86 platform specifically. If you have a driver, you must be able to find out. If you’ve been able to compile our app for the default, you’ve been able to ... and why 32 and 64 bit ACE coexist? - Microsoft Office 2010 or their dependent components, and Microsoft Office 2010 or their dependent components ... Q: Why can't the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft Office 2010 does not support Microsoft Office 2010. It doesn’t support, but it doesn’t support. 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 AccessDatabaseEngine_X64.exe installation with the / passive key, 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 incompatibility issues. When I installed the driver AccessDatabaseEngine_x64.exe ... If office 2010 32-bit is installed and if 64-bit is installed , then ** every ** time you can access the 32-bit drivers for office . In my case (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1), no visible problems (pah-pah-pah) have yet arisen, but once again I want to note that this is all that is called as is and without -or obligations.
I download AccessDatabaseEngine_x64.exe, create a System Restore Point for every fireman (although, in my opinion, when updating office components, it is automatically created anyway), I put it from the command line. Here are the possible keys:
Pic.3
Pic.4
Unlike Figure 2, the installation starts.
Pic.5
and somewhere in half a minute it is dry, in a businesslike way it ends without any fanfare, OK, hurray, I got up, etc. Now we will see what it Sukhov. In Figure 1, from the Providers context menu, we say Refresh and, as they say, we feel the difference.
Pic.6
It infuses. Quickly bungled simple xslx:
Pic.7
In SQL Server we 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 with a sinking read:
select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]') Script 2
Goal? No, FIG!
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider for Microsoft.ACE.OLEDB.12.0 for the linked server XlsLnkSrv reported an error. Did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the OLE DB provider for the data source "Microsoft.ACE.OLEDB.12.0" for the linked server "XlsLnkSrv".
No, (Excel forgot to close) after all 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 need to do first
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 32-bit, 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. ). If you create a connection to the Data Flow DTS package on the same Sample.xslsx, the Microsoft Office 12.0 Access Database Engine OLE DB Provider will fail to initialize, which is logical - since 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 the same place ), but then bye a linked server. To earn a linked server - see the beginning of the article. Something like this. My modest needs were limited at the moment by access to Excel. For example, I did not check access database operation. Provided to readers as an independent exercise. Judging by the information on the connection there is also a place for achievement.
Source: https://habr.com/ru/post/219415/
All Articles