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:



<code>
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
</code>



Version Information 
  * Reported : 11__/23__/2010
  * Version(s): 04.50 +
  * Fixed in :