Loading Excel into DB on 64 bit machine

Loading Excel into DB was much easier but it became almost impossible in SQL 2008 (64 bit machine).
Here is the fix.

Fix perfectly works with “Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)” and “Office 2010”.

One time Activity

1.      1). Install “AccessDatabaseEngine_x64.exe” from here on server

2.      2). Execute following code on Server

USE [master]

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

Your Code

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:\Nisarg\Sample.xlsx','SELECT * FROM [Sheet1$]')