Click here to Skip to main content
11,409,138 members (63,580 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server2008
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 21:59pm
Edited 28-Apr-13 22:00pm

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,676
1 OriginalGriff 6,885
2 Maciej Los 3,322
3 Abhinav S 3,238
4 Peter Leow 3,034

Advertise | Privacy | Mobile
Web04 | 2.8.150414.5 | Last Updated 29 Apr 2013
Copyright © CodeProject, 1999-2015
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