May 18, 2007 at 2:48 am
I have the following four queries which all work except the last one which fails to update the record. By failure I mean the rows affected returned by query analyser is 0 whereas for the first three it is 1. There is a difference in the Execution Plan but the only difference is the number of data items being updated. Can anyone explain and/or make any suggestions for making the update work.
Thanks
UPDATE
dbo.tblBudgetFinal
SET
PackingTypeCode = TotalPRT.PackingTypeCode
FROM
dbo.tblBudgetFinal
JOIN
dbo.vw_BudgetTotalPricesRevenuesTonnes AS TotalPRT
ON
tblBudgetFinal.CustProdID = TotalPRT.CustProdID AND tblBudgetFinal.Year = TotalPRT.Year
Where
TotalPRT.Year = 2006 and TotalPRT.CustProdId = 1371
UPDATE
dbo.tblBudgetFinal
SET
PackingTypeCode = TotalPRT.PackingTypeCode,
UOM = TotalPRT.UOM
FROM
dbo.tblBudgetFinal
JOIN
dbo.vw_BudgetTotalPricesRevenuesTonnes AS TotalPRT
ON
tblBudgetFinal.CustProdID = TotalPRT.CustProdID AND tblBudgetFinal.Year = TotalPRT.Year
Where
TotalPRT.Year = 2006 and TotalPRT.CustProdId = 1371
UPDATE
dbo.tblBudgetFinal
SET
PackingTypeCode = TotalPRT.PackingTypeCode,
UOM = TotalPRT.UOM,
TransportTypeCode = TotalPRT.TransportTypeCode
FROM
dbo.tblBudgetFinal
JOIN
dbo.vw_BudgetTotalPricesRevenuesTonnes AS TotalPRT
ON
tblBudgetFinal.CustProdID = TotalPRT.CustProdID AND tblBudgetFinal.Year = TotalPRT.Year
Where
TotalPRT.Year = 2006 and TotalPRT.CustProdId = 1371
UPDATE
dbo.tblBudgetFinal
SET
PackingTypeCode = TotalPRT.PackingTypeCode,
UOM = TotalPRT.UOM,
TransportTypeCode = TotalPRT.TransportTypeCode,
PackingTypeSize = TotalPRT.PackingTypeSize
FROM
dbo.tblBudgetFinal
JOIN
dbo.vw_BudgetTotalPricesRevenuesTonnes AS TotalPRT
ON
tblBudgetFinal.CustProdID = TotalPRT.CustProdID AND tblBudgetFinal.Year = TotalPRT.Year
Where
TotalPRT.Year = 2006 and TotalPRT.CustProdId = 1371
May 18, 2007 at 5:57 am
Well, I don't know if I am on the right track on this one but I had once a problem with a complicated view that under certain conditions did not work right. It looks like you are selecting your data for update from a view and it might be possible that this view is causing your problem. What I would try to do is update your view to select a fixed value for PackingTypeSize instead of a fleid or calculated field and see if this makes a difference. It might help if you post the definition of your view as well.
And I am assuming you are not getting any error messages from the last query. Right?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 18, 2007 at 6:07 am
Your right in your assumption there are no errors reported by query analyser
The views are quite complicated
The related views and tables are as follows
CREATE VIEW dbo.vw_BudgetTotalPricesRevenuesTonnes
AS
SELECT TOP 100 PERCENT COALESCE (Q.CustProdID, P.CustProdID) AS CustProdID, P.JanTotalPrice, P.JanTotalPrice * Q.January AS JanTotalRev,
dbo.fnConvertQuantities(Q.January, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS JanTotalTonnes, Q.January AS JanTotalVol, P.FebTotalPrice,
P.FebTotalPrice * Q.February AS FebTotalRev, dbo.fnConvertQuantities(Q.February, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS FebTotalTonnes,
Q.February AS FebTotalVol, P.MarTotalPrice, P.MarTotalPrice * Q.March AS MarTotalRev, dbo.fnConvertQuantities(Q.March, Q.PackingTypeSize,
Q.UOM, Q.UnitCode) AS MarTotalTonnes, Q.March AS MarTotalVol, P.AprTotalPrice, P.AprTotalPrice * Q.April AS AprTotalRev,
dbo.fnConvertQuantities(Q.April, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS AprTotalTonnes, Q.April AS AprTotalVol, P.MayTotalPrice,
P.MayTotalPrice * Q.May AS MayTotalRev, dbo.fnConvertQuantities(Q.May, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS MayTotalTonnes,
Q.May AS MayTotalVol, P.JunTotalPrice, P.JunTotalPrice * Q.June AS JunTotalRev, dbo.fnConvertQuantities(Q.June, Q.PackingTypeSize, Q.UOM,
Q.UnitCode) AS JunTotalTonnes, Q.June AS JunTotalVol, P.JulTotalPrice, P.JulTotalPrice * Q.July AS JulTotalRev, dbo.fnConvertQuantities(Q.July,
Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS JulTotalTonnes, Q.July AS JulTotalVol, P.AugTotalPrice, P.AugTotalPrice * Q.August AS AugTotalRev,
dbo.fnConvertQuantities(Q.August, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS AugTotalTonnes, Q.August AS AugTotalVol, P.SepTotalPrice,
P.SepTotalPrice * Q.September AS SepTotalRev, dbo.fnConvertQuantities(Q.September, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS SepTotalTonnes,
Q.September AS SepTotalVol, P.OctTotalPrice, P.OctTotalPrice * Q.October AS OctTotalRev, dbo.fnConvertQuantities(Q.October, Q.PackingTypeSize,
Q.UOM, Q.UnitCode) AS OctTotalTonnes, Q.October AS OctTotalVol, P.NovTotalPrice, P.NovTotalPrice * Q.November AS NovTotalRev,
dbo.fnConvertQuantities(Q.November, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS NovTotalTonnes, Q.November AS NovTotalVol, P.DecTotalPrice,
P.DecTotalPrice * Q.December AS DecTotalRev, dbo.fnConvertQuantities(Q.December, Q.PackingTypeSize, Q.UOM, Q.UnitCode) AS DecTotalTonnes,
Q.December AS DecTotalVol, P.VersionId, P.Year, P.ProductID, P.CustomerID, Q.PackingTypeCode, Q.TransportTypeCode, Q.PackingTypeSize, Q.UOM,
Q.UnitCode, Q.ProductionCode, Q.DespatchCode, Q.SalesRepName, Q.Set_Area, Q.Set_Industry, Q.Set_Product, Q.CustRef
FROM dbo.tblBudgetData AS Q INNER JOIN
dbo.vw_BudgetTotalPrice AS P ON Q.CustProdID = P.CustProdID AND Q.VersionId = P.VersionId
WHERE (Q.ElementTypeId = 2) AND (Q.VersionId IN
(SELECT VersionID
FROM WBBMAPPS.dbo.tblVersions
WHERE (YearVersion = 1) AND (SalesTypeID = 2) AND (IsBaseLine = 0)))
ORDER BY COALESCE (Q.CustProdID, P.CustProdID)
CREATE VIEW [dbo].[vw_BudgetTotalPrice]
AS
SELECT TOP 100 PERCENT CustProdID, SUM(January) AS JanTotalPrice, SUM(February) AS FebTotalPrice, SUM(March) AS MarTotalPrice, SUM(April)
AS AprTotalPrice, SUM(May) AS MayTotalPrice, SUM(June) AS JunTotalPrice, SUM(July) AS JulTotalPrice, SUM(August) AS AugTotalPrice,
SUM(September) AS SepTotalPrice, SUM(October) AS OctTotalPrice, SUM(November) AS NovTotalPrice, SUM(December) AS DecTotalPrice, VersionId,
[Year], CustomerID, ProductID
FROM dbo.tblBudgetData
WHERE (VersionId IN
(SELECT VersionID
FROM WBBMAPPS.dbo.tblVersions
WHERE YearVersion = 1 AND SalesTypeID = 2 AND IsBaseLine = 0)) AND (ElementTypeId <> 2)
GROUP BY CustProdID, VersionId, [Year], CustomerID, ProductID
ORDER BY CustProdID
CREATE TABLE [dbo].[tblBudgetData] (
[DataId] [int] NULL ,
[CustProdID] [int] NULL ,
[GroupCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Class] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConditionCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Year] [int] NULL ,
[January] [decimal](14, 4) NULL ,
[February] [decimal](14, 4) NULL ,
[March] [decimal](14, 4) NULL ,
[April] [decimal](14, 4) NULL ,
[May] [decimal](14, 4) NULL ,
[June] [decimal](14, 4) NULL ,
[July] [decimal](14, 4) NULL ,
[August] [decimal](14, 4) NULL ,
[September] [decimal](14, 4) NULL ,
[October] [decimal](14, 4) NULL ,
[November] [decimal](14, 4) NULL ,
[December] [decimal](14, 4) NULL ,
[CurrentVersion] [bit] NULL ,
[SalesTypeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustRef] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvCustRef] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvCustName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesRepID] [int] NULL ,
[DespatchSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductionSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AS400DivCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConditionName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerID] [int] NULL ,
[ProductID] [int] NULL ,
[GradeId] [int] NULL ,
[GradeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndustryGroupName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndustryGroupID] [int] NULL ,
[Price] [money] NULL ,
[ElementTypeId] [int] NULL ,
[TransportTypeId] [int] NULL ,
[ElementOrder] [int] NULL ,
[Exw] [bit] NULL ,
[ElementTypeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ElementTypeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteID] [int] NULL ,
[JDEAcctCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackingTypeSize] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VersionId] [int] NULL ,
[TransportTypeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransportTypeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackingTypeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DespatchCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductionCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[YearVersion] [bit] NULL ,
[SalesRepName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Industry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Product] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblBudgetFinal] (
[CustProdID] [int] NULL ,
[Year] [int] NULL ,
[JanTotalRev] [decimal](14, 4) NULL ,
[JanTotalVol] [decimal](14, 4) NULL ,
[JanHaulRev] [decimal](14, 4) NULL ,
[JanHaulVol] [decimal](14, 4) NULL ,
[JanInsurRev] [decimal](14, 4) NULL ,
[JanInsurVol] [decimal](14, 4) NULL ,
[JanPackRev] [decimal](14, 4) NULL ,
[JanPackVol] [decimal](14, 4) NULL ,
[JanProdRev] [decimal](14, 4) NULL ,
[JanProdVol] [decimal](14, 4) NULL ,
[FebTotalRev] [decimal](14, 4) NULL ,
[FebTotalVol] [decimal](14, 4) NULL ,
[FebHaulRev] [decimal](14, 4) NULL ,
[FebHaulVol] [decimal](14, 4) NULL ,
[FebInsurRev] [decimal](14, 4) NULL ,
[FebInsurVol] [decimal](14, 4) NULL ,
[FebPackRev] [decimal](14, 4) NULL ,
[FebPackVol] [decimal](14, 4) NULL ,
[FebProdRev] [decimal](14, 4) NULL ,
[FebProdVol] [decimal](14, 4) NULL ,
[MarTotalRev] [decimal](14, 4) NULL ,
[MarTotalVol] [decimal](14, 4) NULL ,
[MarHaulRev] [decimal](14, 4) NULL ,
[MarHaulVol] [decimal](14, 4) NULL ,
[MarInsurRev] [decimal](14, 4) NULL ,
[MarInsurVol] [decimal](14, 4) NULL ,
[MarPackRev] [decimal](14, 4) NULL ,
[MarPackVol] [decimal](14, 4) NULL ,
[MarProdRev] [decimal](14, 4) NULL ,
[MarProdVol] [decimal](14, 4) NULL ,
[AprTotalRev] [decimal](14, 4) NULL ,
[AprTotalVol] [decimal](14, 4) NULL ,
[AprHaulRev] [decimal](14, 4) NULL ,
[AprHaulVol] [decimal](14, 4) NULL ,
[AprInsurRev] [decimal](14, 4) NULL ,
[AprInsurVol] [decimal](14, 4) NULL ,
[AprPackRev] [decimal](14, 4) NULL ,
[AprPackVol] [decimal](14, 4) NULL ,
[AprProdRev] [decimal](14, 4) NULL ,
[AprProdVol] [decimal](14, 4) NULL ,
[MayTotalRev] [decimal](14, 4) NULL ,
[MayTotalVol] [decimal](14, 4) NULL ,
[MayHaulRev] [decimal](14, 4) NULL ,
[MayHaulVol] [decimal](14, 4) NULL ,
[MayInsurRev] [decimal](14, 4) NULL ,
[MayInsurVol] [decimal](14, 4) NULL ,
[MayPackRev] [decimal](14, 4) NULL ,
[MayPackVol] [decimal](14, 4) NULL ,
[MayProdRev] [decimal](14, 4) NULL ,
[MayProdVol] [decimal](14, 4) NULL ,
[JunTotalRev] [decimal](14, 4) NULL ,
[JunTotalVol] [decimal](14, 4) NULL ,
[JunHaulRev] [decimal](14, 4) NULL ,
[JunHaulVol] [decimal](14, 4) NULL ,
[JunInsurRev] [decimal](14, 4) NULL ,
[JunInsurVol] [decimal](14, 4) NULL ,
[JunPackRev] [decimal](14, 4) NULL ,
[JunPackVol] [decimal](14, 4) NULL ,
[JunProdRev] [decimal](14, 4) NULL ,
[JunProdVol] [decimal](14, 4) NULL ,
[JulTotalRev] [decimal](14, 4) NULL ,
[JulTotalVol] [decimal](14, 4) NULL ,
[JulHaulRev] [decimal](14, 4) NULL ,
[JulHaulVol] [decimal](14, 4) NULL ,
[JulInsurRev] [decimal](14, 4) NULL ,
[JulInsurVol] [decimal](14, 4) NULL ,
[JulPackRev] [decimal](14, 4) NULL ,
[JulPackVol] [decimal](14, 4) NULL ,
[JulProdRev] [decimal](14, 4) NULL ,
[JulProdVol] [decimal](14, 4) NULL ,
[AugTotalRev] [decimal](14, 4) NULL ,
[AugTotalVol] [decimal](14, 4) NULL ,
[AugHaulRev] [decimal](14, 4) NULL ,
[AugHaulVol] [decimal](14, 4) NULL ,
[AugInsurRev] [decimal](14, 4) NULL ,
[AugInsurVol] [decimal](14, 4) NULL ,
[AugPackRev] [decimal](14, 4) NULL ,
[AugPackVol] [decimal](14, 4) NULL ,
[AugProdRev] [decimal](14, 4) NULL ,
[AugProdVol] [decimal](14, 4) NULL ,
[SepTotalVol] [decimal](14, 4) NULL ,
[SepTotalRev] [decimal](14, 4) NULL ,
[SepHaulVol] [decimal](14, 4) NULL ,
[SepHaulRev] [decimal](14, 4) NULL ,
[SepInsurRev] [decimal](14, 4) NULL ,
[SepInsurVol] [decimal](14, 4) NULL ,
[SepPackRev] [decimal](14, 4) NULL ,
[SepPackVol] [decimal](14, 4) NULL ,
[SepProdRev] [decimal](14, 4) NULL ,
[SepProdVol] [decimal](14, 4) NULL ,
[OctTotalRev] [decimal](14, 4) NULL ,
[OctTotalVol] [decimal](14, 4) NULL ,
[OctHaulRev] [decimal](14, 4) NULL ,
[OctHaulVol] [decimal](14, 4) NULL ,
[OctInsurRev] [decimal](14, 4) NULL ,
[OctInsurVol] [decimal](14, 4) NULL ,
[OctPackRev] [decimal](14, 4) NULL ,
[OctPackVol] [decimal](14, 4) NULL ,
[OctProdRev] [decimal](14, 4) NULL ,
[OctProdVol] [decimal](14, 4) NULL ,
[NovTotalRev] [decimal](14, 4) NULL ,
[NovTotalVol] [decimal](14, 4) NULL ,
[NovHaulRev] [decimal](14, 4) NULL ,
[NovHaulVol] [decimal](14, 4) NULL ,
[NovInsurRev] [decimal](14, 4) NULL ,
[NovInsurVol] [decimal](14, 4) NULL ,
[NovPackRev] [decimal](14, 4) NULL ,
[NovPackVol] [decimal](14, 4) NULL ,
[NovProdRev] [decimal](14, 4) NULL ,
[NovProdVol] [decimal](14, 4) NULL ,
[DecTotalRev] [decimal](14, 4) NULL ,
[DecTotalVol] [decimal](14, 4) NULL ,
[DecHaulRev] [decimal](14, 4) NULL ,
[DecHaulVol] [decimal](14, 4) NULL ,
[DecInsurRev] [decimal](14, 4) NULL ,
[DecInsurVol] [decimal](14, 4) NULL ,
[DecPackRev] [decimal](14, 4) NULL ,
[DecPackVol] [decimal](14, 4) NULL ,
[DecProdRev] [decimal](14, 4) NULL ,
[DecProdVol] [decimal](14, 4) NULL ,
[PackingTypeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransportTypeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackingTypeSize] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductionCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DespatchCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesRepName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Industry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Set_Product] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustRef] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JanTprtVol] [numeric](18, 4) NULL ,
[JanTprtRev] [numeric](18, 4) NULL ,
[FebTprtVol] [numeric](18, 4) NULL ,
[FebTprtRev] [numeric](18, 4) NULL ,
[MarTprtVol] [numeric](18, 4) NULL ,
[MarTprtRev] [numeric](18, 4) NULL ,
[AprTprtVol] [numeric](18, 4) NULL ,
[AprTprtRev] [numeric](18, 4) NULL ,
[MayTprtVol] [numeric](18, 4) NULL ,
[MayTprtRev] [numeric](18, 4) NULL ,
[JunTprtVol] [numeric](18, 4) NULL ,
[JunTprtRev] [numeric](18, 4) NULL ,
[JulTprtVol] [numeric](18, 4) NULL ,
[JulTprtRev] [numeric](18, 4) NULL ,
[AugTprtVol] [numeric](18, 4) NULL ,
[AugTprtRev] [numeric](18, 4) NULL ,
[SepTprtVol] [numeric](18, 4) NULL ,
[SepTprtRev] [numeric](18, 4) NULL ,
[OctTprtVol] [numeric](18, 4) NULL ,
[OctTprtRev] [numeric](18, 4) NULL ,
[NovTprtVol] [numeric](18, 4) NULL ,
[NovTprtRev] [numeric](18, 4) NULL ,
[DecTprtVol] [numeric](18, 4) NULL ,
[DecTprtRev] [numeric](18, 4) NULL ,
[JanDistVol] [numeric](18, 4) NULL ,
[JanDistRev] [numeric](18, 4) NULL ,
[FebDistVol] [numeric](18, 4) NULL ,
[FebDistRev] [numeric](18, 4) NULL ,
[MarDistVol] [numeric](18, 4) NULL ,
[MarDistRev] [numeric](18, 4) NULL ,
[AprDistVol] [numeric](18, 4) NULL ,
[AprDistRev] [numeric](18, 4) NULL ,
[MayDistVol] [numeric](18, 4) NULL ,
[MayDistRev] [numeric](18, 4) NULL ,
[JunDistVol] [numeric](18, 4) NULL ,
[JunDistRev] [numeric](18, 4) NULL ,
[JulDistVol] [numeric](18, 4) NULL ,
[JulDistRev] [numeric](18, 4) NULL ,
[AugDistVol] [numeric](18, 4) NULL ,
[AugDistRev] [numeric](18, 4) NULL ,
[SepDistVol] [numeric](18, 4) NULL ,
[SepDistRev] [numeric](18, 4) NULL ,
[OctDistVol] [numeric](18, 4) NULL ,
[OctDistRev] [numeric](18, 4) NULL ,
[NovDistVol] [numeric](18, 4) NULL ,
[NovDistRev] [numeric](18, 4) NULL ,
[DecDistVol] [numeric](18, 4) NULL ,
[DecDistRev] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData] ON [dbo].[tblBudgetData]([CustProdID], [VersionId], [Year], [CustomerID], [ProductID]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_1] ON [dbo].[tblBudgetData]([ElementTypeId]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_2] ON [dbo].[tblBudgetData]([CustProdID]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_3] ON [dbo].[tblBudgetData]([CustProdID], [Year]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_4] ON [dbo].[tblBudgetData]([CustProdID], [Year], [VersionId]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_5] ON [dbo].[tblBudgetData]([CustProdID], [VersionId]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblBudgetData_6] ON [dbo].[tblBudgetData]([Year]) ON [PRIMARY]
GO
GO
CREATE INDEX [IX_tblBudgetFinal] ON [dbo].[tblBudgetFinal]([CustProdID], [Year]) ON [PRIMARY]
GO
May 18, 2007 at 6:43 am
Maybe you can try to create a simpler vw_BudgetTotalPricesRevenuesTonnes view by removing all the fields you don't need for your update and see if this makes a difference. If the problem is related to some bug in SQL Server you will have to create a workaround and you will have to experiment a lot to figure out the correct approach.
What happens if you select those 4 fields you use for update from your view using the same filter as in your update? Do you get any records back? If you do, try to insert them into a temp table to see if you really are getting them. I had a case that the QA was showing the recordset returned but anytime you tried to use the data it wasn't really there.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 18, 2007 at 7:02 am
I am only showing 4 entries because I was trying to find when the update stopped working. The live Update command uses all of the fields so I can't modify the view.
Using a select command works OK with the four fields and also works for all the fields using Select * from ...
May 18, 2007 at 7:26 am
Did you try to insert the records retrieved from the view to a temp table to see if all of them are getting processed?
Another suggestion could be to eliminate as much as possible from the view for example why do you have TOP 100 percent?, remove the order by replace the IN in the WHERE with a join.
The update code is correct so it is something weird with the views.
I know you may not be able to remove all the fields from the view for your final code but in order to find the workaround you may have to start disecting the views.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 18, 2007 at 7:30 am
I will do some testing over the week end. I'll post what I find out.
Thanks for your help.
May 21, 2007 at 9:05 am
Just an update
I removed the 'Order By ' in the views and all seemed to work correctly.
Thanks for your help
May 21, 2007 at 9:15 am
I am glad I could help a bit. Views can be very sensitive and tricky. Sometimes they don't aways work as you would expect them to, especially when you start joining them.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply