I am presently doing migration of SQL Server 2005 to SQL Server 2012. Mostly all the functionality remains the same. However, in few store I was using Jet provider 4.0 to import Excel data into the SQL Server table. I know it is not being supported for 64 bits & Ace provider is an alternate for the same. I searched on net & luckily found query for the same. However, query works only for sysadmin (SA) access, which will not be provided in the production.
Does anybody have the solution for the same ?
Below is the query I tried for the same.
USE Master
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Cutomer_Transaction.xls;Extended Properties=Excel 12.0')...[Sheet1$]
GO
This query works fine for sa login.. but fails for other user with below message.
"Ad Hoc access to oledb Provider 'Microsoft.Ace.Oledb.12.0' has been denied. You must access this provider through linked server."