Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied

[flickimp]

Trying to open a SQL Server query but am getting this message:

Ad hoc access to OLE DB provider 'Microsoft.Jet. OLEDB.4.0' has been denied. You must access this provider through a linked server.

Heres the script:

Insert into OpenDataSource( 
	'Microsoft.Jet.OLEDB.4.0',
  	'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
	Extended properties=Excel 5.0')...[PivotSh$]
(Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month, Qtr,
	Description_Contract, Activity_Type_Grouped, Sum_of_Spells)

Select	Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
	Case	When Financial_Month between '1' and '3' Then 'Qtr 1'
		When Financial_Month between '4' and '6' Then 'Qtr 2'
		When Financial_Month between '7' and '9' Then 'Qtr 3'
		When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End as Qtr,
	Description_Contract,
	Case	When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
		Else Activity_Type End as Activity_Type_Grouped,
	SUM(Total_Spells) as Sum_of_Spells
	
From	[Busobjects].Capacity_Planning.dbo.tbl_BaseLine_UHL_Spells_Trend_0708 a

Inner 	Join OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  			'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
			Extended properties=Excel 5.0')...[SPECIALTY TREND by Month$] as PR
 	On PR.P1 = Commissioner_Code 
	AND PR.P2 = LSS_Flag
	AND PR.P3 = Description_Contract
	AND PR.P4 = (Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
	Else Activity_Type END)
	

Left	Outer Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_Specialty_UHL b
	on a.Specialty_Code = b.Code

Left	Outer Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_SpecServ_0708 c
	on a.Specialised_Services_Code = c.SpecServ

Left	Outer Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_HRG_Tariff_0708 d
	on a.HRG_Code = d.HRGCode

Group	by Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
	Case	When Financial_Month between '1' and '3' Then 'Qtr 1'
		When Financial_Month between '4' and '6' Then 'Qtr 2'
		When Financial_Month between '7' and '9' Then 'Qtr 3'
		When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End,
	/*Specialty_Code,*/ Description_Contract, --Activity_Type,
	Case	When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
		Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
	--Specialised_Services_Code + ': '+c.Description

[deepuv04]

Try the following query to read the data from excel sheet

	sp_configure 'show advanced options', 1
	RECONFIGURE
	GO
	sp_configure 'Ad Hoc Distributed Queries', 1
	RECONFIGURE
	GO

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\Administrator\Desktop\rolemenu.xls',
                'SELECT * FROM [Sheet1$] where MenuItemId is not null')

make sure your excelsheet is on server...

for more information visit the following link

** Edit **
Removed link to competing forum site. Please check out our FAQ.

[Santhanams]

If i run the OpenRowSet Query under sysadmin role it is working perfectly. But if the same query running under public role, then it is not working. It is giving following message like "Ad hoc access to OLE DB provider 'Microsoft.Jet. OLEDB.4.0' has been denied". I am trying to use least privilage account for my application. Do we need sysadmin role to run OpenRowSet query? Kindly help me out to resolve the Issue. Thanks in advance