Error Message

An error has occurred while updating the ““InventoryLog”” table from version 4 to 5. || || 'ROW_NUMBER' is not a recognized function name“

Explanation of Error

The update query is using a SQL function called ROW_NUMBER to number the results in order. The function does not exists in SQL Server 2000, only SQL Server 2005 and later versions.

Severity

High. User is down.

Possible Root Cause(s)

Resolution

Set the InventoryLog table version to 5.

Then run the following query:

DECLARE @StartDate DATETIME; DECLARE @EndDate DATETIME; SET @StartDate  '01/01/2010' ;
SET @EndDate  '01/01/2100' ;
DECLARE @VersionList TABLE (ID INT PRIMARY KEY, LoginDateTime DATETIME,VersionNumber VARCHAR(15), Series INT);
INSERT INTO @VersionList (ID, LoginDateTime, VersionNumber) SELECT ID,
StartDateTime, SUBSTRING(Notes, PATINDEX( '%Version:%' , Notes)+10, 15) AS VersionNumber FROM Journal
WHERE JournalActivityType  30 AND StartDateTime BETWEEN @StartDate AND @EndDate;
DECLARE MyCursor CURSOR FOR SELECT VersionNumber FROM @VersionList ORDER BY ID;
DECLARE @MyVersion VARCHAR(15);DECLARE @CurrentVersion VARCHAR(15);SET @CurrentVersion  '';
DECLARE @CurrentSeries INT;SET @CurrentSeries  0;
OPEN MyCursor FETCH NEXT FROM MyCursor INTO @MyVersion WHILE @@FETCH_STATUS  0
BEGIN  IF @CurrentVersion  @MyVersion
  SET @CurrentSeries  @CurrentSeries+1;
  SET @CurrentVersion  @MyVersion;
  UPDATE @VersionList
  SET Series  @CurrentSeries
  WHERE CURRENT OF MyCursor;
  FETCH NEXT FROM MyCursor INTO @MyVersion
END
CLOSE MyCursor
DEALLOCATE MyCursor      ;
Declare @MinimumDate DateTime;
set @MinimumDate  (
select min(FirstLogin) from (
SELECT Min(LoginDateTime) FirstLogin, Max(LoginDateTime) LastLogin, VersionNumber
FROM @VersionList T
GROUP BY Series, VersionNumber
) VersionHistory
where VersionNumber > '04.40.1004.2901')
;
DECLARE @MinumumDate DATETIME;
SET @MinumumDate  (SELECT TOP 1 LoginDateTime FROM @VersionList);
--select @MinimumDate
-- Update QuantityReserved, Available, Expected
Update InventoryLog
Set QuantityReserved  0, QuantityAvailable  0, QuantityExpected  0
Where transdetailid in (
 select id from transdetail where transheaderid in (
   select id from transheader where
      ordercreateddate > @MinimumDate or
      (builtdate is null or  builtdate > @MinimumDate) and
      (saledate is null or  saledate > @MinimumDate) and
      (closeddate is null or  closeddate > @MinimumDate) and
      (voideddate is null or  voideddate > @MinimumDate)
      and ID > -1 ) )
  and QuantityReserved  0 and QuantityBilled  0 and QuantityReceivedOnly  0
  and QuantityAvailable  0 and QuantityOnhand  0
  and QuantityExpected  0 and QuantityOnOrder  0
--
Declare @InventoryLogIDs Table (ID Int Primary Key);
Insert into @InventoryLogIDs ( ID )
  select max(id) as id from inventorylog il where transdetailid in (
   select id from transdetail where transheaderid in (
     select id from transheader where
       ordercreateddate > @MinimumDate or
       (builtdate is null or  builtdate > @MinimumDate) and
       (saledate is null or  saledate > @MinimumDate) and
       (closeddate is null or  closeddate > @MinimumDate) and
       (voideddate is null or  voideddate > @MinimumDate)
        and ID > -1 ) )
  group by partid, transdetailid, transpartID ;
Update InventoryLog
Set QuantityReserved  TP.EstimatedValue - TP.ActualValue,
    QuantityAvailable  -(TP.EstimatedValue - TP.ActualValue) ,
    QuantityExpected  -(TP.EstimatedValue - TP.ActualValue)
From InventoryLog IL left join TransPart tp on tp.ID  IL.TransPartID
Where IL.ID in (select ID from @InventoryLogIDs) and TP.EstimatedValue > TP.ActualValue

Version Information

• Reported : 11/23/2010
• Version(s): 04.50 +
• Fixed in :