Click here to Skip to main content
15,920,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
SQL
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."
Posted

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900