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?