OLE DB provider “Microsoft.ACE.OLEDB.12.0” for lin

Question

I want export data from MSSQL SERVER 2008 from Excel, but i have error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 
returned message "Bookmark is invalid.".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 
could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". 

its my excel file on export (file save in format 97-2003)

its my query

Tell me please why i get errors? What me need doing?

Answer 1

I have office 32 bit version installed and Sql server 2008 r2 (64 bit) installed. I tried installing the 64 bit version of the AccessDatabaseEngine redistributable but it wouldnt let me. I then tried to install the 32 bit version but then sql server was complaining:

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

So then I was resigned to the installing office 64 bit. Then I noticed the "Import and Export Data (32 bit option)" in the "Microsoft Sql server 2008 r2" startup menu. So I tried that and it worked for me.

I had been trying to import the data, by right clicking on the "database -> Tasks - Import Data.." which was obviously opening up the 64 bit version and causing me pain.

Answer 2

Check/Set the registry key below manually (exact path is specific to your SQL server version):

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001

It seems to be a problem to use Jet 4.0 and ACE.OLEDB.12 concurrently.

Answer 3

Try this

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 8.0;Database=C:\Export.xls;', 
                       'SELECT id_sale FROM [ExportSheet$]')
SELECT id_sale
FROM dbo.Sale

OR

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Export.xls;Extended Properties=EXCEL 8.0')...[ExportSheet$]
SELECT id_sale
FROM dbo.Sale

Answer 4

After many struggles with this issue, I found the following solution:

1. On 64-bit servers and boxes, you need to first UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components. Yes, its a headache but the only way I found to install the new replacements for the JET engine components that need to run on 64-bit machines.
2. Download and install the new component from Microsoft: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
   • This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.

3. Open up SQL Server and run the following:

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1;
   GO
   RECONFIGURE;
   GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
   GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
   GO
   

   • This sets the parameters needed to access and run queries related to the components. Address ‘null

4. Now, if you are running OPENROWSET calls you need to abandon calls ,made using the old JET parameters and use the new calls as follows:

   (*Example, importing an EXCEL file directly into SQL):
   DONT DO THIS….
   SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=c:\PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')
   
   USE THIS INSTEAD…
   SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')
   
   *At this point resolved two SQL issues and ran perfectly
   

5. Now for the fun part…..find all your Office Disks and reinstall Office and/or applications needed back onto the machine. You can install the 64- bit version of Office 10 by going onto the disk and going into the 64-bit folder and running it but beware as in some cases some third party apps dont interface yet with that version of Office.

Answer 5

1- Install AccessDatabaseEngine_x64.exe or AccessDatabaseEngine.exe if you are using 32 bit version of windows (Restart is required).

2- If you are using SQL Management Studio, run Management Studio as administrator. If you don’t run it as an administrator account you will have this error (Cannot initialize the data source object of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".

3- Run this command to configure your Database

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE with override;
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE with override;
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

4- Don’t use ‘select * from sheet1$’ in OPENROWSET function. Try this code.

select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;DATABASE=D:\test.xlsx', sheet1$)

If still you have (32 bit) error, try to restart your SQL service and check your C:\Windows\Temp and see do you have access to this directory or not.