Click here to Skip to main content
12,998,363 members (58,157 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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  
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters',1  
sp_configure 'show advanced options', 1  
sp_configure 'Ad Hoc Distributed Queries', 1  

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,
((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

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)".
Posted 28-Apr-13 20:59pm
Updated 28-Apr-13 21:00pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170622.1 | Last Updated 29 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100