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