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

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)

SelectProvider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen 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,
CaseWhen 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_Tre nd_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)

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

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

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

Groupby Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen 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,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
--Specialised_Services_Code + ': '+c.Description

Quote:

Originally Posted by 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)

SelectProvider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen 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,
CaseWhen 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_Tre nd_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)

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

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

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

Groupby Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen 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,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
--Specialised_Services_Code + ': '+c.Description

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

http://www.databasejournal.com/features/mssql/article.php/3692831