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
