无法初始化OLE DB提供程序“Microsoft.Jet.OLEDB.4.0”的数据源对象。对于链接服务器“（null）”。 [英] Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

问题描述

将查询结果导出到 excel 2010时出现以下错误

getting below error while exporting query result to excel 2010

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1  
  
GO   
  
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters',1  
  
GO  
  
sp_configure 'show advanced options', 1  
GO  
RECONFIGURE WITH OverRide  
GO  
sp_configure 'Ad Hoc Distributed Queries', 1  
GO  
RECONFIGURE WITH OverRide  


Insert into tbl_Purchase (Purchase,YarnID)
select SUM(YarnTransQuantity) as Purchase , YarnID --into #Purchases
from YarnTransaction where YarnTransTypeID=4
group by YarnID

Insert into tbl_Sale (Sale,YarnID)
select SUM(YarnTransQuantity) as Sale , YarnID --into #Sales
from YarnTransaction where deptToID=13
group by YarnID

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DataBase=C:\Users\Murtaza\Desktop\YMSLatest\YMS 7-10-2012\Costing.xls;',
'SELECT * FROM [Costing$]')
Select Distinct Y.YarnCode,YarnCodeDesc,Sum(YarnTransQuantity) as MonthOpening,OpeningRate,
Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity)) as OpeningValue,
isnull(Purchase,0.00) as Purchase,PurchaseValue,SUM(isnull(yarnConsumption,0.00)) as Consumption,
Convert(decimal(18,2),(((OpeningRate*Sum(YarnTransQuantity))+PurchaseValue)/isnull((Sum(YarnTransQuantity)+Purchase),1)*SUM(isnull(yarnConsumption,1)))) as ConsumptionValue
,isnull(Sale,0.00) as Sale,SaleValue,isnull(SaleValue,0.00)-isnull(Sale,0.00)*((Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00))/(Sum(YarnTransQuantity)+Purchase)) as ProfitOnSale,
(Sum(YarnTransQuantity)+isnull(Purchase,0.00))-(SUM(isnull(yarnConsumption,0.00)))-isnull(Sale,0.00) as KGClosing,
Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00)-
Convert(decimal(18,2),(((OpeningRate*Sum(YarnTransQuantity))+PurchaseValue)/isnull((Sum(YarnTransQuantity)+Purchase),1)*SUM(isnull(yarnConsumption,1))))-
((OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00))/((Sum(YarnTransQuantity)+isnull(Purchase,0.00)))*(isnull(Sale,0.00)) as ClosingValue
from YarnTransaction YT
inner join Yarn Y on YT.YarnID=Y.YarnID
inner join YarnCode YC on Yc.YarnId=YT.YarnID
inner join tbl_OpeningRates ORa on ORa.YarnId=YT.YarnID
left join tbl_Purchase P on P.YarnId=YT.YarnID
left join CalculateConsumption CC on CC.YarnId=YT.YarnID
left join tbl_Sale S on S.YarnId=YT.YarnID
left join tbl_CostingValues CV on CV.YarnCode=Y.YarnCode
Group By Y.YarnCode,YarnCodeDesc,OpeningRate,Purchase,Sale,yt.ColorCodeID,PurchaseValue,SaleValue

消息7399，16级，状态1，行42

OLE DB提供程序Microsoft.Jet.OLEDB .4.0对于链接服务器（null）报告错误。提供商没有提供有关错误的任何信息。

消息7303，级别16，状态1，行42

无法初始化OLE DB提供程序的数据源对象Microsoft .Jet.OLEDB.4.0for linked server（null）。

Msg 7399, Level 16, State 1, Line 42
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 42
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

推荐答案

'）
选择 Distinct Y.YarnCode，YarnCodeDesc，Sum（YarnTransQuantity） as MonthOpening， OpeningRate，
转换（十进制（ 10 ， 2 ），OpeningRate * Sum（YarnTransQuantity）） as OpeningValue，
isnull （购买， 0 。 00 ） as Purchase，PurchaseValue，SUM（isnull（yarnConsumption， 0 。 00 ）） as 消耗，
转换（ decimal （ 18 ， 2 ），（（（OpeningRate * Sum（YarnTransQuantity））+ PurchaseValue）/ isnull（（Sum（ YarnTransQuantity）+ Purchase）， 1 ）* SUM（isnull（yarnConsumption， 1 ）））） as ConsumptionValue
，isnull（Sale， 0 。 00 ） as Sale，SaleValue，isnull（SaleValue， 0 。 00 ） - isnull（Sale， 0 。 00 ）*（（ 转换（十进制（ 10 ， 2 ），OpeningRate * Sum（YarnTransQuantity））+ isnull（PurchaseValue， 0 。 00 ））/（Sum（YarnTransQuantity）+ Purchase）） ProfitOnSale，
（ Sum（YarnTransQuantity）+ isnull（购买， 0 。 00 ）） - （SUM（isnull）（yarnConsumption， 0 。 00 ））） - isnull（Sale， 0 。 00 ） as KGClosing，
转换（十进制（ 10 ， 2 ），OpeningRate * Sum（YarnTransQuantity））+ isnull（PurchaseValue， 0 。 00 ） -
转换（ decimal （ 18 ， 2 ），（（（OpeningRate * Sum（YarnTransQuantity））+ PurchaseValue）/ isnull（（Sum（YarnTransQuantity）） +购买）， 1 ）* SUM（isnull（yarnConsumption， 1 ）））） -
（（OpeningRate * Sum（YarnTransQuantity））+ isnull（PurchaseValue， 0 。 00 ））/（ （Sum（YarnTransQuantity）+ isnull（购买， 0 。 00 ）））*（isnull（Sale， 0 。 00 ）） as ClosingValue
来自 YarnTransaction YT
inner join Y Y YT.YarnID = Y.YarnID
inner join YarnCode YC Yc.YarnId = YT.YarnID
内部 join tbl_OpeningRates ORa on O Ra.YarnId = YT.YarnID
left join tbl_Purchase P on P.YarnId = YT.YarnID
left join CalculateConsumption CC CC.YarnId = YT.YarnID
left join tbl_Sale S on S.YarnId = YT.YarnID
left join tbl_CostingValues CV CV.YarnCode = Y.YarnCode
组 按 Y.YarnCode，YarnCodeDesc，OpeningRate，Purchase，Sale，yt.ColorCodeID，PurchaseValue，SaleValue

') Select Distinct Y.YarnCode,YarnCodeDesc,Sum(YarnTransQuantity) as MonthOpening,OpeningRate, Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity)) as OpeningValue, isnull(Purchase,0.00) as Purchase,PurchaseValue,SUM(isnull(yarnConsumption,0.00)) as Consumption, Convert(decimal(18,2),(((OpeningRate*Sum(YarnTransQuantity))+PurchaseValue)/isnull((Sum(YarnTransQuantity)+Purchase),1)*SUM(isnull(yarnConsumption,1)))) as ConsumptionValue ,isnull(Sale,0.00) as Sale,SaleValue,isnull(SaleValue,0.00)-isnull(Sale,0.00)*((Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00))/(Sum(YarnTransQuantity)+Purchase)) as ProfitOnSale, (Sum(YarnTransQuantity)+isnull(Purchase,0.00))-(SUM(isnull(yarnConsumption,0.00)))-isnull(Sale,0.00) as KGClosing, Convert(Decimal(10,2),OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00)- Convert(decimal(18,2),(((OpeningRate*Sum(YarnTransQuantity))+PurchaseValue)/isnull((Sum(YarnTransQuantity)+Purchase),1)*SUM(isnull(yarnConsumption,1))))- ((OpeningRate*Sum(YarnTransQuantity))+isnull(PurchaseValue,0.00))/((Sum(YarnTransQuantity)+isnull(Purchase,0.00)))*(isnull(Sale,0.00)) as ClosingValue from YarnTransaction YT inner join Yarn Y on YT.YarnID=Y.YarnID inner join YarnCode YC on Yc.YarnId=YT.YarnID inner join tbl_OpeningRates ORa on ORa.YarnId=YT.YarnID left join tbl_Purchase P on P.YarnId=YT.YarnID left join CalculateConsumption CC on CC.YarnId=YT.YarnID left join tbl_Sale S on S.YarnId=YT.YarnID left join tbl_CostingValues CV on CV.YarnCode=Y.YarnCode Group By Y.YarnCode,YarnCodeDesc,OpeningRate,Purchase,Sale,yt.ColorCodeID,PurchaseValue,SaleValue

< br $>




消息7399，16级，状态1，行42

OLE DB提供程序Microsoft.Jet .OLEDB.4.0用于链接服务器（null）报告了一个呃ROR。提供商没有提供有关错误的任何信息。

消息7303，级别16，状态1，行42

无法初始化OLE DB提供程序的数据源对象Microsoft .Jet.OLEDB.4.0for linked server（null）。

Msg 7399, Level 16, State 1, Line 42
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 42
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

这篇关于无法初始化OLE DB提供程序“Microsoft.Jet.OLEDB.4.0”的数据源对象。对于链接服务器“（null）”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！