I am trying to create a procedure that import an excel sheet in my SQL Server database with SSMS (I am admin).
I have no problem when I do the import manually; my excel file is closed and I can modify it.
When I run this query :
CREATE PROCEDURE test
AS
BEGIN
INSERT INTO dbo.[Disp$] (Disp, Betrieb)
SELECT Disp, betrieb
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=Downloads\MyExcelFile.xlsx', 'SELECT * FROM [Disp$]');
END;
I get this message :
Msg 7302, Level 16, State 1, Procedure test, Line 4 [Batch Start Line 0]
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Here is what I have tried :
-
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1 -
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO -
EXEC master.sys.sp_MSset_oledb_prop; HDR=YES;IMEX=1
I have also tried uninstalling and reinstalling SQL Server, SSMS and Microsoft Access database engine.
I tried installing everything in 32 bits since SSMS is only available in 32 bits, but SQL Server 2014 32 bits is not supported on 64 bits.