The 32-bit OLE DB provider “Microsoft.Jet.OLEDB.4.0” cannot be loaded in-process on a 64-bit SQL Server

Question

Hi,

I get this error :

The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server

in Microsoft SQL Manager 2014(on Windows 10) when running this StoredProcedure :

    	--Allow for SQL to use cmd shell
	EXEC sp_configure 'show advanced options', 1;    -- To allow advanced options to be changed.
	RECONFIGURE; -- To update the currently configured value for advanced options.
	EXEC sp_configure 'xp_cmdshell', 1; -- To enable cmd 
	RECONFIGURE;
	EXEC sp_configure 'Ad Hoc Distributed Queries', 1; --To enable to execute OPENROWSET.
	RECONFIGURE;
	EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
	EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
	RECONFIGURE;
	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
	RECONFIGURE;

	--Loop through all of the files
	CREATE TABLE #files(excelFileName VARCHAR(100));
	INSERT INTO #files
	EXEC xp_cmdshell 'dir /B "C:\temp\"';

	declare @fileName varchar(100)

	declare @SQLcommand nvarchar(max)

	While (Select Count(*) From #files where excelFileName is not null) > 0
	Begin

		Select Top 1 @fileName = excelFileName From #files;

		if(@fileName LIKE '%.csv' OR @fileName LIKE '%.xlsx')
			begin
				SET @SQLcommand = 'select * into #MyAppORsuiteInt FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @fileName + ';HDR=YES'', ''SELECT * FROM [Sheet1$]'');';
				EXEC (@SQLcommand)

				SELECT * from ##MyappORsuiteInt;

				PRINT('FOUND : ' + @filename);
			end
		

		-- OPENROWSET processing goes here, using @fileName to identify which file to use


		Delete from #files Where excelFileName = @FileName;

	End

I have reinstalled office to office 2016 64 bit(dubble checked). The Microsoft SQL Server 11.0.2100.60 64 bit is running on an other Microsoft Server 2012.

I have also installed the Microsoft Access Database Engine 2010 Redistributable on both local server.

The folder that it will read the file from has the SQL account setup.

So why do I still get this error?

Answer 1

Hello,

The old JET data provider is only available as 32 bit Driver and can't be used with a 64 bit application as SQL Server. You have to use the 64 bit ACE Provider instead, as you already have in part of your script.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Thanks! That solved the problem. I got another error but that will be in another thread if I dont find a solution.