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

  • 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?

    Thursday, June 8, 2017 7:51 AM

Answers

  • 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]

    • Marked as answer by SnowJim Thursday, June 8, 2017 12:04 PM
    Thursday, June 8, 2017 8:12 AM

All replies

  • 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]

    • Marked as answer by SnowJim Thursday, June 8, 2017 12:04 PM
    Thursday, June 8, 2017 8:12 AM
  • Thanks! That solved the problem. I got another error but that will be in another thread if I dont find a solution.
    Thursday, June 8, 2017 12:04 PM