I encountered an issue, that is known by Sage, with Sage 200 SP2. The issue is with one of the Update Scripts, and the following exception error ocurrs when updating the Company database in System Administration:

 <Message>Failed to run SQL script ‘SchemaMigration’ against company dataset ‘CompanyX’ on server ‘SAGESERVER’. The Exception was: SQL script execution failed. Inner Exception:</Message>

 <Source>Sage.Manufacturing.Database.Sql</Source>

Sage are aware of this problem which they think is due to an issue with inserting BOM document lines into a new table.  The Manufacturing script ‘rolls back’ and so the update process does not complete.

Be aware that the error produced is a generic message which can relate to other problems. 

Sage have released two scripts to help with the identifying and fixing the BOMDocumentLines issue. The ‘FindBOMDocumentLinesToMigrate.sql’ script checks whether there are any BOM Document Lines that need to be migrated.  If there are lines present, you have established that the issue is as above. You are then able to run the ‘UpdateBOMDocumentLines-v2011SP2.sql’ script BEFORE updating the Company database. Bothe scripts are detailled below.

Please note that these scripts should only be used once 2011 SP2 has been installed.

FindBOMDocumentLinesToMigrate.sql

SELECT BomBuildPackageID, BomLineSequence, DocumentURL
FROM BOMLine_MIG
      INNER JOIN BOM_MIG ON BOM_MIG.bOMid = BOMLine_MIG.BOMID
      INNER JOIN StockItem ON StockItem.ItemID = BOM_MIG.StockItemID
      INNER JOIN BomRecord ON BomRecord.Reference = StockItem.Code
      INNER JOIN BomBuildPackage ON BomBuildPackage.BomRecordID = BOMRecord.BomRecordID
WHERE BomBuildPackageTypeID = 0 and  BOMLineTypeID = 2

 UpdateBOMDocumentLines-v2011SP2.sql

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spr_GetNextPK]’) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
BEGIN
EXECUTE(‘CREATE PROCEDURE [dbo].[spr_GetNextPK](@Value bigint output) AS
BEGIN
 DECLARE @Counter bigint
 SET @Counter = 1 + (SELECT TOP 1 NextValue FROM Counter)
 UPDATE Counter SET NextValue = @Counter

–Return value
SET @Value = @Counter
END’)
END

— MIGRATE MMS BOM Document Lines
IF NOT EXISTS(SELECT * FROM BOMComponentLine WHERE BOMComponentLineTypeID = 4)
BEGIN
 IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BOMLine_MIG]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
 BEGIN

  DECLARE BOMDocumentLineCursor CURSOR FOR
   SELECT BomBuildPackageID, BomLineSequence, DocumentURL
   FROM BOMLine_MIG
    INNER JOIN BOM_MIG ON BOM_MIG.bOMid = BOMLine_MIG.BOMID
    INNER JOIN StockItem ON StockItem.ItemID = BOM_MIG.StockItemID
    INNER JOIN BomRecord ON BomRecord.Reference = StockItem.Code
    INNER JOIN BomBuildPackage ON BomBuildPackage.BomRecordID = BOMRecord.BomRecordID
   WHERE BomBuildPackageTypeID = 0 and  BOMLineTypeID = 2

  DECLARE @BomDocumentBuildPackageID BIGINT
  DECLARE @BomDocumentLineSequence BIGINT
  DECLARE @DocumentURL VARCHAR(255)
   
    
  OPEN BOMDocumentLineCursor
   FETCH BOMDocumentLineCursor INTO @BomDocumentBuildPackageID, @BomDocumentLineSequence, @DocumentURL

  WHILE @@FETCH_STATUS = 0
  BEGIN
   DECLARE @bomDocumentCounter bigint
          
   exec spr_GetNextPK @bomDocumentCounter OUTPUT
     
   –add the comment
   INSERT INTO BomComponentLine(BomComponentLineID, BomBuildPackageID, SequenceNumber, BomComponentLineTypeID,FixedQuantity, Suspended,Notes, DocumentFile, BomCommentLine, ShowCommentOnReport)
    VALUES(@bomDocumentCounter, @BomDocumentBuildPackageID, @BomDocumentLineSequence,4, 0,0, ”, @DocumentURL, ”,0 )
      
   FETCH BOMDocumentLineCursor INTO @BomDocumentBuildPackageID, @BomDocumentLineSequence, @DocumentURL
  END
            
  — Close and deallocate cursor
  CLOSE BOMDocumentLineCursor
  DEALLOCATE BOMDocumentLineCursor
 END
END

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spr_GetNextPK]’) AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE [dbo].[spr_GetNextPK]
GO

Advertisements