/*
** Wonderware - InSQL 9.0
** Migration script for InSQL 8.0 to 9.0 upgrade
**------------------------------------------------------------------------
** DO NOT RUN THIS SCRIPT AGAINST AN ALREADY CONFIGURED INSQL 9.0 DATABASE
** THIS SCRIPT CAN RUN AGAINST A NEWLY-BUILT RUNTIME DATABASE
**------------------------------------------------------------------------
**
** THIS SCRIPT DOES NOT MIGRATE THE HISTORY DATA (Event / Summary / Manual)
** Run the MigrateSQLHistData80to90.sql once this migration has been 
**    completed if you did not allow its execution during the installation.
**
*/

use Runtime
go

set NOCOUNT ON
go

/* Add IOServerTypes which might not be in 9.0 release list */
BEGIN TRAN
insert IOServerType (ApplicationName, Description, ExeName, Revision, Platform)
  select ApplicationName, Description, ExeName, Revision, Platform
     from Runtime80.dbo.IOServerType
          where ApplicationName not in 
             (select ApplicationName from Runtime.dbo.IOServerType)
COMMIT TRAN
go

/* IOServer <SysDrv is excluded> */
BEGIN TRAN
delete IOServer where IOServerKey > 2

SET IDENTITY_INSERT IOServer ON

insert IOServer (IOServerKey, StorageNodeKey, IODriverKey, ApplicationName, Path, ComputerName, AltComputerName,
                 AutoStart, ExeType, InitializationStatus, ProtocolType, Description)
   select IOServerKey, StorageNodeKey, IODriverKey, ApplicationName, Path, ComputerName, AltComputerName,
          AutoStart, ExeType, InitializationStatus, ProtocolType, Description
      from Runtime80.dbo.IOServer
         where ApplicationName not in ('SysDrv', 'InSQL_MDAS')

SET IDENTITY_INSERT IOServer OFF
COMMIT TRAN
go

/* IODriver */
BEGIN TRAN
delete IODriver where IODriverKey > 2

alter table IODriver disable trigger tI_IODriver
SET IDENTITY_INSERT IODriver ON

insert IODriver (IODriverKey, StorageNodeKey, ComputerName, AltComputerName, 
                 StoreForwardMode, StoreForwardPath, MinMBThreshold)
   select IODriverKey, StorageNodeKey, ComputerName, AltComputerName, 
          StoreForwardMode, StoreForwardPath, MinMBThreshold
      from Runtime80.dbo.IODriver
         where IODriverKey > 2

SET IDENTITY_INSERT IODriver OFF
alter table IODriver enable trigger tI_IODriver

COMMIT TRAN
go

/* Topic */
BEGIN TRAN

SET IDENTITY_INSERT Topic ON

insert Topic (TopicKey, IOServerKey, StorageNodeKey, Name, TimeOut)
   select TopicKey, IOServerKey , StorageNodeKey, Name, TimeOut
      from Runtime80.dbo.Topic
         where IOServerKey > 2  -- Don't need SysDrv or MDAS Topics 

SET IDENTITY_INSERT Topic OFF

COMMIT TRAN
go

/* Storage Location */
BEGIN TRAN
update StorageLocation set Path = o.Path,
                           MaxMBSize = o.MaxMBSize,
                           MinMBThreshold = o.MinMBThreshold,
                           SortOrder = o.SortOrder
   from Runtime.dbo.StorageLocation n, Runtime80.dbo.StorageLocation o
      where n.StorageType = o.StorageType 
COMMIT TRAN
go

/* Message */
BEGIN TRAN
insert Message (Message0, Message1)
   select Message0, Message1
      from Runtime80.dbo.Message
         where Message0 + ' ' + Message1 <> ALL
                  (SELECT Message0 + ' ' + Message1 FROM Runtime.dbo.Message)
COMMIT TRAN
go

/* EngineeringUnit */
BEGIN TRAN
insert EngineeringUnit (Unit, DefaultTagRate)
   select Unit, DefaultTagRate
      from Runtime80.dbo.EngineeringUnit
         where Unit <> ALL
                  (SELECT Unit FROM Runtime.dbo.EngineeringUnit)
COMMIT TRAN
go

/* We must ensure that the wwTagKey is preserved. */
/* InSQL 8.0 has 99 system tags; InSQL 9.0 has >100. */

/* Delete the InSQL 9.0 specific system tags - and re-create them later */
delete Tag
  where TagName like 'Sys%'
  and wwTagKey >= 100

/* Tag: System tags associated with remote IDASs */
BEGIN TRAN
SET IDENTITY_INSERT Tag ON
insert into Tag (TagName, IOServerKey, StorageNodeKey, wwTagKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage, ItemName, StorageRate,
			AIRetrievalMode, CalculatedAISamples)
   select tFrom.TagName, tpTo.IOServerKey, tFrom.StorageNodeKey, tFrom.wwTagKey, tpTo.TopicKey, tFrom.Description,
          tFrom.AcquisitionType, tFrom.StorageType, tFrom.AcquisitionRate, tFrom.TagType,
          tFrom.TimeDeadband, tFrom.DateCreated, tFrom.CreatedBy, 
          tFrom.CurrentEditor, tFrom.SamplesInActiveImage, tFrom.ItemName,
          tFrom.StorageRate, tFrom.AIRetrievalMode, tFrom.CalculatedAISamples
      from Runtime80.dbo.Tag tFrom, Runtime80.dbo.Topic tpFrom, Runtime80.dbo.IOServer IOFrom,
           Runtime.dbo.Topic tpTo, Runtime.dbo.IOServer IOTo
         where  tFrom.TopicKey = tpFrom.TopicKey
         and    tFrom.TagType in (1, 2)  -- Just Ana, Discrete tags
         and    tFrom.IOServerKey = 1  -- Just System Tags
         and    tpFrom.IOServerKey = IOFrom.IOServerKey
         and    tFrom.IOServerKey = IOFrom.IOServerKey
         and    IOFrom.ApplicationName = IOTo.ApplicationName
         and    tpFrom.Name            = tpTo.Name
         and    tpTo.IOServerKey     = IOTo.IOServerKey
         and    (tFrom.TagName like 'SysDataAc%'  -- Just IDAS perf and status tags
         or     tFrom.TagName like 'SysPerfDataAcq%')
         and    tFrom.TagName not in (select TagName from Runtime.dbo.Tag)
SET IDENTITY_INSERT Tag OFF
COMMIT TRAN

/* Tag: Analog, Discrete and String - MDAS tags only */
BEGIN TRAN
SET IDENTITY_INSERT Tag ON
insert into Tag (TagName, IOServerKey, StorageNodeKey, wwTagKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage, ItemName, StorageRate,
			AIRetrievalMode, CalculatedAISamples)
   select tFrom.TagName, tpTo.IOServerKey, tFrom.StorageNodeKey, tFrom.wwTagKey, tpTo.TopicKey, tFrom.Description,
          tFrom.AcquisitionType, tFrom.StorageType, tFrom.AcquisitionRate, tFrom.TagType,
          tFrom.TimeDeadband, tFrom.DateCreated, tFrom.CreatedBy, 
          tFrom.CurrentEditor, tFrom.SamplesInActiveImage, tFrom.ItemName,
          tFrom.StorageRate, tFrom.AIRetrievalMode, tFrom.CalculatedAISamples
      from Runtime80.dbo.Tag tFrom, Runtime80.dbo.Topic tpFrom, Runtime80.dbo.IOServer IOFrom,
           Runtime.dbo.Topic tpTo, Runtime.dbo.IOServer IOTo
         where  tFrom.TopicKey = tpFrom.TopicKey
         and    tFrom.TagType < 4      -- Just Ana, Discrete and String tags
         and    tFrom.IOServerKey = 2  -- Just MDAS Tags
         and    tpFrom.IOServerKey = IOFrom.IOServerKey
         and    tFrom.IOServerKey = IOFrom.IOServerKey
         and    IOFrom.ApplicationName = IOTo.ApplicationName
         and    tpFrom.Name            = tpTo.Name
         and    tpTo.IOServerKey     = IOTo.IOServerKey
SET IDENTITY_INSERT Tag OFF
COMMIT TRAN

/* Tag: Analog, Discrete and String - I/O server tags only */
BEGIN TRAN
SET IDENTITY_INSERT Tag ON
insert into Tag (TagName, IOServerKey, StorageNodeKey, wwTagKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage, ItemName, StorageRate,
			AIRetrievalMode, CalculatedAISamples)
   select tFrom.TagName, tpTo.IOServerKey, tFrom.StorageNodeKey, tFrom.wwTagKey, tpTo.TopicKey, tFrom.Description,
          tFrom.AcquisitionType, tFrom.StorageType, tFrom.AcquisitionRate, tFrom.TagType,
          tFrom.TimeDeadband, tFrom.DateCreated, tFrom.CreatedBy, 
          tFrom.CurrentEditor, tFrom.SamplesInActiveImage, tFrom.ItemName,
          tFrom.StorageRate, tFrom.AIRetrievalMode, tFrom.CalculatedAISamples
      from Runtime80.dbo.Tag tFrom, Runtime80.dbo.Topic tpFrom, Runtime80.dbo.IOServer IOFrom,
           Runtime.dbo.Topic tpTo, Runtime.dbo.IOServer IOTo
         where  tFrom.TopicKey = tpFrom.TopicKey
         and    tFrom.TagType < 4      -- Just Ana, Discrete and String tags
         and    tFrom.IOServerKey > 2  -- No System or MDAS Tags
         and    tpFrom.IOServerKey = IOFrom.IOServerKey
         and    tFrom.IOServerKey = IOFrom.IOServerKey
         and    IOFrom.ApplicationName = IOTo.ApplicationName
         and    IOFrom.ComputerName    = IOTo.ComputerName
         and    tpFrom.Name            = tpTo.Name
         and    tpTo.IOServerKey     = IOTo.IOServerKey
SET IDENTITY_INSERT Tag OFF
COMMIT TRAN

/* Tag: Other types */
BEGIN TRAN
SET IDENTITY_INSERT Tag ON
insert into Tag (TagName, IOServerKey, StorageNodeKey, wwTagKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage)
   select tFrom.TagName, NULL, 1, tFrom.wwTagKey, NULL, tFrom.Description,
          tFrom.AcquisitionType, tFrom.StorageType, tFrom.AcquisitionRate, tFrom.TagType,
          tFrom.TimeDeadband, tFrom.DateCreated, tFrom.CreatedBy, 
          tFrom.CurrentEditor, tFrom.SamplesInActiveImage
      from Runtime80.dbo.Tag tFrom
         where tFrom.TagType > 3
         and   tFrom.TagName <> ALL
                  (select TagName from Runtime.dbo.Tag where TagType > 3)
SET IDENTITY_INSERT Tag OFF
COMMIT TRAN

/* AnalogTag */
BEGIN TRAN
insert into AnalogTag (TagName, EUKey, MinEU, MaxEU, MinRaw, MaxRaw,
                       Scaling, RawType, ValueDeadband, InitialValue,
                       IntegerSize, SignedInteger)
   select aFrom.TagName, uTo.EUKey, MinEU, MaxEU, MinRaw, MaxRaw,
          Scaling, RawType,
          CASE
             WHEN ValueDeadband < 0 THEN 0
             WHEN ValueDeadband > 100 THEN 100
             ELSE ValueDeadband
          END, InitialValue,
          IntegerSize, SignedInteger
      from Runtime80.dbo.AnalogTag aFrom, Runtime80.dbo.Tag tFrom,
           Runtime80.dbo.EngineeringUnit uFrom, Runtime.dbo.EngineeringUnit uTo
         where aFrom.TagName = tFrom.TagName
         and   tFrom.IOServerKey = 1   -- System tags
         and   aFrom.EUKey = uFrom.EUKey
         and   uFrom.Unit  = uTo.Unit
         and   (tFrom.TagName like 'SysDataAc%'  -- Just IDAS perf and status tags
         or    tFrom.TagName like 'SysPerfDataAcq%')
         and   tFrom.TagName not in (select TagName from Runtime.dbo.AnalogTag)
   UNION ALL
   select aFrom.TagName, uTo.EUKey, MinEU, MaxEU, MinRaw, MaxRaw,
          Scaling, RawType,
          CASE
             WHEN ValueDeadband < 0 THEN 0
             WHEN ValueDeadband > 100 THEN 100
             ELSE ValueDeadband
          END, InitialValue,
          IntegerSize, SignedInteger
      from Runtime80.dbo.AnalogTag aFrom, Runtime80.dbo.Tag tFrom,
           Runtime80.dbo.EngineeringUnit uFrom, Runtime.dbo.EngineeringUnit uTo
         where aFrom.TagName = tFrom.TagName
         and   tFrom.IOServerKey > 1   -- MDAS and I/O server tags
         and   aFrom.EUKey = uFrom.EUKey
         and   uFrom.Unit  = uTo.Unit
COMMIT TRAN
go

/* DiscreteTag */
BEGIN TRAN
insert into DiscreteTag (TagName, MessageKey, InitialValue)
   select dFrom.TagName, mTo.MessageKey, InitialValue
      from Runtime80.dbo.DiscreteTag dFrom, Runtime80.dbo.Tag tFrom,
           Runtime80.dbo.Message mFrom, Runtime.dbo.Message mTo
         where dFrom.TagName = tFrom.TagName
         and   tFrom.IOServerKey = 1   -- System tags
         and   dFrom.MessageKey = mFrom.MessageKey
         and   mFrom.Message0 + ' ' + mFrom.Message1 = mTo.Message0 + ' ' + mTo.Message1
         and   tFrom.TagName like 'SysDataAc%'  -- Just IDAS status tags
         and   tFrom.TagName not in (select TagName from Runtime.dbo.DiscreteTag)
   UNION ALL
   select dFrom.TagName, mTo.MessageKey, InitialValue
      from Runtime80.dbo.DiscreteTag dFrom, Runtime80.dbo.Tag tFrom,
           Runtime80.dbo.Message mFrom, Runtime.dbo.Message mTo
         where dFrom.TagName = tFrom.TagName
         and   tFrom.IOServerKey > 1  -- MDAS and I/O server tags
         and   dFrom.MessageKey = mFrom.MessageKey
         and   mFrom.Message0 + ' ' + mFrom.Message1 = mTo.Message0 + ' ' + mTo.Message1
COMMIT TRAN
go

/* StringTag */
BEGIN TRAN
insert into StringTag (TagName, InitialValue, MaxLength, DoubleByte)
   select sFrom.TagName, InitialValue, MaxLength, DoubleByte
      from Runtime80.dbo.StringTag sFrom, Runtime80.dbo.Tag tFrom
      where sFrom.TagName = tFrom.TagName
      and tFrom.IOServerKey > 1  -- MDAS and I/O server tags
COMMIT TRAN
go

/* Add the two new InSQL 9.0 system tags per IDAS */
BEGIN TRAN
declare @iodrivertag nvarchar(256)
declare @description nvarchar(512)
declare @iodriverstring nvarchar(10)
declare @computername nvarchar(256)
declare @INSiodrivertag nvarchar(256)
declare @INSdescription nvarchar(512)

declare migratedidas cursor for
  select RTRIM(TagName), RTRIM(Description) from Tag
    where TagName like 'SysDataAcqRxItemsPerSec%'
    order by wwTagKey

open migratedidas
fetch next from migratedidas into @iodrivertag, @description

while @@FETCH_STATUS = 0
begin
  set @iodriverstring = REPLACE(@iodrivertag, 'SysDataAcqRxItemsPerSec', '')
  set @computername   = REPLACE(@description, 'Items per second received from ', '')

  set @INSiodrivertag = RTRIM('SysDataAcq' + LTRIM(RTRIM(@iodriverstring)) + 'BadValues')
  set @INSdescription = RTRIM('Bad quality points per second for Data Acquisition on ' + LTRIM(RTRIM(@computername)))

  EXEC aaAnalogTagInsert
    @TagName         = @INSiodrivertag,
    @TopicKey        = 1,
    @IOServerKey     = 1,
    @Description     = @INSdescription,
    @AcquisitionType = 3,
    @StorageType     = 1,
    @StorageRate     = 5000,
    @TimeDeadband    = 0,
    @ItemName        = @INSiodrivertag,
    @EUKey           = 2,
    @MinEU           = 0,
    @MaxEU           = 1000000,
    @MinRaw          = 0,
    @MaxRaw          = 1000000,
    @Scaling         = 0,
    @RawType         = 3,
    @IntegerSize     = 32,
    @SignedInteger   = 0

  INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
    SELECT 13, wwDomainTagKey
    FROM   TagRef
    WHERE  TagName = @INSiodrivertag

  set @INSiodrivertag = RTRIM('SysDataAcq' + LTRIM(RTRIM(@iodriverstring)) + 'OutsideRealtime')
  set @INSdescription = RTRIM('Discarded points per second for Data Acquisition on ' + LTRIM(RTRIM(@computername)))

  EXEC aaAnalogTagInsert
    @TagName         = @INSiodrivertag,
    @TopicKey        = 1,
    @IOServerKey     = 1,
    @Description     = @INSdescription,
    @AcquisitionType = 3,
    @StorageType     = 1,
    @StorageRate     = 5000,
    @TimeDeadband    = 0,
    @ItemName        = @INSiodrivertag,
    @EUKey           = 2,
    @MinEU           = 0,
    @MaxEU           = 1000000,
    @MinRaw          = 0,
    @MaxRaw          = 1000000,
    @Scaling         = 0,
    @RawType         = 3,
    @IntegerSize     = 32,
    @SignedInteger   = 0

  INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
    SELECT 13, wwDomainTagKey
    FROM   TagRef
    WHERE  TagName = @INSiodrivertag

  fetch next from migratedidas into @iodrivertag, @description
end

close migratedidas
deallocate migratedidas

COMMIT TRAN
go

/* Re-insert the extra InSQL 9.0 system tags */
BEGIN TRAN
DECLARE @NoUnit int
DECLARE @PerCentUnit int
DECLARE @BytesUnit int
select @NoUnit = EUKey from EngineeringUnit where Unit = 'None'
select @PerCentUnit = EUKey from EngineeringUnit where Unit = '%'
select @BytesUnit = EUKey from EngineeringUnit where Unit = 'Bytes'

-- Indexing Service --------------------------------------------------
EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingCPU',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = '%CPU load for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = 'SysPerfIndexingCPU',
  @EUKey           = @PerCentUnit,
  @MinEU           = 0,
  @MaxEU           = 100,
  @MinRaw          = 0,
  @MaxRaw          = 100,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingPrivateBytes',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = 'Private Bytes for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = 'SysPerfIndexingPrivateBytes',
  @EUKey           = @BytesUnit,
  @MinEU           = 0,
  @MaxEU           = 600000000,
  @MinRaw          = 0,
  @MaxRaw          = 600000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingVirtualBytes',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = 'Virtual Bytes for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = 'SysPerfIndexingVirtualBytes',
  @EUKey           = @BytesUnit,
  @MinEU           = 0,
  @MaxEU           = 600000000,
  @MinRaw          = 0,
  @MaxRaw          = 600000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingThreadCount',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Thread count for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfIndexingThreadCount',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 128,
  @MinRaw          = 0,
  @MaxRaw          = 128,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingHandleCount',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Handle count for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfIndexingHandleCount',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 20000,
  @MinRaw          = 0,
  @MaxRaw          = 20000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfIndexingPageFaults',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Page faults per second for Historian Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfIndexingPageFaults',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 256,
  @MinRaw          = 0,
  @MaxRaw          = 256,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 16, wwDomainTagKey
  FROM   TagRef
  WHERE  TagName LIKE 'SysPerfIndexing%'

EXEC aaDiscreteTagInsert 
  @TagName         = 'SysIndexing',
  @TopicKey        = 1, 
  @IOServerKey     = 1, 
  @Description     = N'System Indexing Service Status', 
  @AcquisitionType = 3, 
  @StorageType     = 2, 
  @StorageRate     = 0, 
  @ItemName        = N'SysIndexing',
  @MessageKey      = 1

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 15, wwDomainTagKey
  FROM   TagRef
  WHERE  TagName = 'SysIndexing'

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfAvailableBytes',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Available physical memory',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfAvailableBytes',
  @EUKey           = @BytesUnit,
  @MinEU           = 0,
  @MaxEU           = 4000000000,
  @MinRaw          = 0,
  @MaxRaw          = 4000000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfMemoryPages',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Pages per second read from or written to disk to resolve hard page faults',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfMemoryPages',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 10000,
  @MinRaw          = 0,
  @MaxRaw          = 10000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysPerfDiskTime',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'% of elapsed time that system disk drives are busy servicing read or write requests',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysPerfDiskTime',
  @EUKey           = @PerCentUnit,
  @MinEU           = 0,
  @MaxEU           = 100,
  @MinRaw          = 0,
  @MaxRaw          = 100,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 16, wwDomainTagKey
  FROM   TagRef
  WHERE  TagName IN ('SysPerfAvailableBytes', 'SysPerfMemoryPages', 'SysPerfDiskTime')

EXEC aaAnalogTagInsert
  @TagName         = 'SysHistoryCacheUsed',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Bytes used for history block cache',
  @AcquisitionType = 3,
  @StorageType     = 2,
  @StorageRate     = 0,
  @TimeDeadband    = 0,
  @ItemName        = N'SysHistoryCacheUsed',
  @EUKey           = @BytesUnit,
  @MinEU           = 0,
  @MaxEU           = 3000000000,
  @MinRaw          = 0,
  @MaxRaw          = 3000000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysHistoryCacheFaults',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'History blocks loaded from disk per minute',
  @AcquisitionType = 3,
  @StorageType     = 2,
  @StorageRate     = 0,
  @TimeDeadband    = 0,
  @ItemName        = N'SysHistoryCacheFaults',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 1000,
  @MinRaw          = 0,
  @MaxRaw          = 1000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysHistoryClients',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Number of clients connected to the Indexing Service',
  @AcquisitionType = 3,
  @StorageType     = 2,
  @StorageRate     = 0,
  @TimeDeadband    = 0,
  @ItemName        = N'SysHistoryClients',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 100,
  @MinRaw          = 0,
  @MaxRaw          = 100,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 17, wwDomainTagKey
  FROM   TagRef
  WHERE  TagName IN ( 'SysHistoryCacheUsed', 'SysHistoryCacheFaults', 'SysHistoryClients')

EXEC aaAnalogTagInsert
  @TagName         = 'SysDataAcq0BadValues',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = 'Bad quality points per second for MDAS Data Acquisition',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = 'SysDataAcq0BadValues',
  @EUKey           = 2,
  @MinEU           = 0,
  @MaxEU           = 1000000,
  @MinRaw          = 0,
  @MaxRaw          = 1000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysDataAcq0OutsideRealtime',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = 'Discarded points per second for MDAS Data Acquisition',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 5000,
  @TimeDeadband    = 0,
  @ItemName        = 'SysDataAcq0OutsideRealtime',
  @EUKey           = 2,
  @MinEU           = 0,
  @MaxEU           = 1000000,
  @MinRaw          = 0,
  @MaxRaw          = 1000000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 32,
  @SignedInteger   = 0

EXEC aaAnalogTagInsert
  @TagName         = 'SysDataAcqOverallItemsPerSec',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Items per second received from all sources',
  @AcquisitionType = 3,
  @StorageType     = 1,
  @StorageRate     = 10000,
  @TimeDeadband    = 0,
  @ItemName        = N'SysDataAcqOverallItemsPerSec',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 100000,
  @MinRaw          = 0,
  @MaxRaw          = 100000,
  @Scaling         = 0,
  @RawType         = 2,
  @IntegerSize     = 0,
  @SignedInteger   = 0

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 13, wwDomainTagKey
  FROM   TagRef
  WHERE  TagName IN ('SysDataAcq0BadValues', 'SysDataAcq0OutsideRealtime', 'SysDataAcqOverallItemsPerSec')

EXEC aaAnalogTagInsert
  @TagName         = 'SysRateDeadbandForcedValues',
  @TopicKey        = 1,
  @IOServerKey     = 1,
  @Description     = N'Total forced values for all tags configured for rate-deadband filtering, since startup',
  @AcquisitionType = 3,
  @StorageType     = 2,
  @StorageRate     = 0,
  @TimeDeadband    = 0,
  @ItemName        = N'SysRateDeadbandForcedValues',
  @EUKey           = @NoUnit,
  @MinEU           = 0,
  @MaxEU           = 1000,
  @MinRaw          = 0,
  @MaxRaw          = 1000,
  @Scaling         = 0,
  @RawType         = 3,
  @IntegerSize     = 16,
  @SignedInteger   = 0

INSERT INTO PublicGroupTag (NameKey, wwDomainTagKey)
  SELECT 17, wwDomainTagKey 
  FROM   TagRef 
  WHERE  TagName = 'SysRateDeadbandForcedValues'

COMMIT TRAN
go

/* Context */
BEGIN TRAN
delete from Context  -- Reset everything

SET IDENTITY_INSERT Context ON

insert Context (ContextKey, Description)
  select ContextKey, Description from Runtime80.dbo.Context

SET IDENTITY_INSERT Context OFF
COMMIT TRAN
go

/* RateOfChange */
BEGIN TRAN
delete from RateOfChange -- Reset everything

insert into RateOfChange
   select TagName, ContextKey, Value, TimeBase, Priority, Checked
      from Runtime80.dbo.RateOfChange
COMMIT TRAN
go

/* Deviation */
BEGIN TRAN
delete from Deviation -- Reset everything

insert into Deviation
   select TagName, ContextKey, MinorDeviation, MinorChecked, MinorPriority,
          MajorDeviation, MajorChecked, MajorPriority, Target, Deadband
      from Runtime80.dbo.Deviation
COMMIT TRAN
go

/* LimitName */
BEGIN TRAN
delete from LimitName  -- Reset everything

SET IDENTITY_INSERT LimitName ON

insert LimitName (LimitNameKey, Name)
  select LimitNameKey, Name from Runtime80.dbo.LimitName

SET IDENTITY_INSERT LimitName OFF
COMMIT TRAN
go

/* Limit */
BEGIN TRAN
delete from Limit  -- Reset everything

insert into Limit
   select TagName, ContextKey, LimitType, Value, LimitNameKey,
          Priority, Checked, Description
      from Runtime80.dbo.Limit 

COMMIT TRAN
go

/* Event SubSystem */
/* DetectorType */
BEGIN TRAN
insert into DetectorType (Name, Description, EditorClassName, DetectorClassName,
                          ExecutionMode)
   select Name, Description, EditorClassName, DetectorClassName, ExecutionMode
      from Runtime80.dbo.DetectorType
      where DetectorTypeKey > 6  -- only detector created by user
COMMIT TRAN
go

/* ActionType */
BEGIN TRAN
insert into ActionType (Name, Description, EditorClassName, ActionClassName)
   select Name, Description, EditorClassName, ActionClassName
      from Runtime80.dbo.ActionType
      where ActionTypeKey > 7  -- only action created by user
COMMIT TRAN
go

/* EventTag */
BEGIN TRAN
insert into EventTag (TagName, DetectorTypeKey, ActionTypeKey, ScanRate,
                      TimeDeadband, Logged, Status, PostDetectorDelay,
                      UseThreadPool, DetectorString, ActionString,
                      Priority, Edge)
   select eFrom.TagName, DetectorTypeKey, ActionTypeKey, ScanRate, 
          eFrom.TimeDeadband, Logged, eFrom.Status, PostDetectorDelay, UseThreadPool,
          DetectorString, ActionString, 0, Edge
      from Runtime80.dbo.EventTag eFrom, Runtime80.dbo.Tag tFrom
         where eFrom.TagName = tFrom.TagName
         and eFrom.DetectorTypeKey <= 6 -- no migration of user's detectors
         and eFrom.ActionTypeKey <= 7   -- no migration of user's actions
         and eFrom.TagName <> ALL
             (select TagName from Runtime.dbo.EventTag)


SET IDENTITY_INSERT TimeDetectorDetail ON

insert into TimeDetectorDetail (TimeDetectorDetailKey, TagName, FrequencyID, Periodicity, StartDateTime, 
                                RunTimeDay, RunTimeHour, RunTimeMin)
   select TimeDetectorDetailKey, tFrom.TagName, FrequencyID, Periodicity, StartDateTime, 
          RunTimeDay, RunTimeHour, RunTimeMin
      from Runtime80.dbo.TimeDetectorDetail tFrom, Runtime80.dbo.EventTag eFrom
         where tFrom.TagName <> ALL
             (select TagName from Runtime.dbo.TimeDetectorDetail)
         and eFrom.TagName = tFrom.TagName
         and eFrom.DetectorTypeKey = 6

SET IDENTITY_INSERT TimeDetectorDetail OFF

COMMIT TRAN
go 

/* Update EventTag.DetectorString to use brand-neutral stored procedure */
BEGIN TRAN

declare @thetag nvarchar(256)
declare @query  nvarchar(1500)
declare @nquery nvarchar(1500)
declare @start  int
declare @qlen   int

declare thedetector cursor for
  select TagName, DetectorString from EventTag
     where DetectorTypeKey in (4, 5) -- Analog/Discrete SpecificValue 
     and TagName <> 'SysStatusEvent'
     and lower(DetectorString) like '%ww_eventdetection%'

open thedetector
fetch next from thedetector into @thetag, @query

while @@FETCH_STATUS = 0
begin
   set @start = charindex('ww_eventdetection', lower(@query))
   set @qlen  = len(@query)

   -- replace 'ww_eventdetection'
   set @nquery = left(@query, @start-1) + 'aaEventDetection' + substring(@query, @start+17, @qlen-@start-16)

   update EventTag set DetectorString = RTRIM(@nquery)
      where TagName = @thetag  

   fetch next from thedetector into @thetag, @query
end

close thedetector
deallocate thedetector

COMMIT TRAN
go

/* Update EventTag.ActionString snapshots and summary to use brand-neutral stored procedure */
BEGIN TRAN
update EventTag set ActionString = 
'exec aaEventSnapshotInsert @EventLogKey, "@EventTime", "@EventTagName"'  
where ActionTypeKey = 3
and TagName <> 'SysStatusEvent'
go

update EventTag set ActionString = 
'exec aaSummaryActionInsert "@EventTagName", "@EventTime"'
where ActionTypeKey = 6
and TagName <> 'SysStatusEvent'
go

/* Set EventTag Status as per prior to the migration */
/* (Insert trigger would have modified those) */
update EventTag set Status = eFrom.Status
   from Runtime80.dbo.EventTag eFrom, Runtime.dbo.EventTag eTo
      where eFrom.TagName = eTo.TagName

COMMIT TRAN
go


/* SnapshotTag */
delete from SnapshotTag
   where EventTagName <> 'SysStatusEvent'
go
BEGIN TRAN
SET IDENTITY_INSERT SnapshotTag ON

insert into SnapshotTag (SnapshotTagKey, TagName, EventTagName, TagType) 
   select SnapshotTagKey, tTo.TagName, EventTagName, tTo.TagType
      from Runtime80.dbo.SnapshotTag stFrom, Runtime.dbo.Tag tTo
        where tTo.TagName = stFrom.TagName          -- Make sure that the tag is still there
        and stFrom.EventTagName <> 'SysStatusEvent'

SET IDENTITY_INSERT SnapshotTag OFF

COMMIT TRAN
go

/* InTouchNode */
BEGIN TRAN
insert into InTouchNode (NodeKey, MachineName, ApplicationName, Path, Description,
                         DuplicateChar, PrefixOrSuffix, AlwaysModifyName,
                         ImportPlantTags, ImportMemoryTags, FixedStorageRate,
                         ImportRoute)
   select NodeKey, MachineName, ApplicationName, Path, Description,
          DuplicateChar, PrefixOrSuffix, AlwaysModifyName,
          ImportPlantTags, ImportMemoryTags, FixedStorageRate,
          ImportRoute
      from Runtime80.dbo.InTouchNode
go
/* TopicImportInfo */
insert into TopicImportInfo (NodeKey, DdeSourceKey, SourceName, ApplicationName,
                             TopicName, RequestInitialData, AlwaysAdvise,
                             DefaultStorageRate, TimeDeadband, Import,
                             ProtocolType)
   select NodeKey, DdeSourceKey, SourceName, ApplicationName,
          TopicName, RequestInitialData, AlwaysAdvise,
          DefaultStorageRate, TimeDeadband, Import, ProtocolType
      from Runtime80.dbo.TopicImportInfo
go
/* InTouchSpecific */
insert into InTouchSpecific (NodeKey, TagName, OriginalName, TypeInfo,
                             InInSQL, Comment)
   select NodeKey, TagName, OriginalName, TypeInfo, InInSQL, Comment
      from Runtime80.dbo.InTouchSpecific
COMMIT TRAN
go

/* PublicNameSpace */
BEGIN TRAN
/* This next step is necessary to allow Children to be inserted at the same time as the Parent */
alter table PublicNameSpace disable trigger tI_PublicNameSpace
set IDENTITY_INSERT PublicNameSpace ON
go
insert into PublicNameSpace (NameKey, Type, ParentKey, Name, ConfigStor)
   select NameKey, Type, ParentKey, Name, ConfigStor
      from Runtime80.dbo.PublicNameSpace
         where NameKey > 17
go
set IDENTITY_INSERT PublicNameSpace OFF
alter table PublicNameSpace enable trigger tI_PublicNameSpace
COMMIT TRAN
go

/* Public Group Tag */
/* Same rule as above for NameKey */
/* For wwDomainTagKey, we need to validate with the TagName (no guarantee that it is the same number) */
BEGIN TRAN
insert into PublicGroupTag (NameKey, wwDomainTagKey)
   select pgtFrom.NameKey,
          trTo.wwDomainTagKey
      from Runtime80.dbo.PublicGroupTag pgtFrom, Runtime80.dbo.TagRef trFrom,
           Runtime.dbo.TagRef trTo
         where trFrom.ServerKey = trTo.ServerKey
         and   trFrom.TagName   = trTo.TagName
         and   pgtFrom.wwDomainTagKey = trFrom.wwDomainTagKey
         -- And we cannot get duplication...
         and  ( RTRIM(CAST(pgtFrom.NameKey as varchar(10))) + ' ' + RTRIM(CAST(trTo.wwDomainTagKey as varchar(10))) )
                 <> ALL
                 (select RTRIM(CAST(NameKey as varchar(10))) + ' ' + RTRIM(CAST(wwDomainTagKey as varchar(10)))
                     from PublicGroupTag) 
COMMIT TRAN
go

/* TagGroup */
BEGIN TRAN
insert into TagGroup (GroupID, Description, CreatedDate, CreatedBy, Type)
   select GroupID, Description, CreatedDate, CreatedBy, Type
      from Runtime80.dbo.TagGroup
         where GroupID > 1  -- 1 is created by Runtime.sql for System Tags 
go
/* Group Tag List */
insert into GroupTagList (GroupID, wwDomainTagKey, Triggerval)
   select gtlFrom.GroupID, trTo.wwDomainTagKey, gtlFrom.Triggerval
      from Runtime80.dbo.GroupTagList gtlFrom, Runtime80.dbo.TagRef trFrom,
           Runtime.dbo.TagRef trTo
         where trFrom.ServerKey = trTo.ServerKey
         and   trFrom.TagName   = trTo.TagName
         and   gtlFrom.wwDomainTagKey = trFrom.wwDomainTagKey
         and   gtlFrom.GroupID > 1  -- see above
COMMIT TRAN
go

/* sysusers */
/* need to add all custom users from Runtime80 */

declare @name sysname
declare userlist cursor for
  select name from Runtime80.dbo.sysusers
      where name not in (select name from Runtime.dbo.sysusers)
	  and uid <> gid

open userlist
fetch next from userlist into @name
while @@FETCH_STATUS = 0
BEGIN
/* Verify that the login corresponding to the user exists on the server.   */
/* It probably will not if this is a migration to a new server.            */
    if exists(select name from master..syslogins where name = @name)
       exec sp_grantdbaccess @name, @name
	else
	   print 'InSQL 8.0 user not added: ' + @name
    fetch next from userlist into @name
END
close userlist
deallocate userlist

/* sysmembers */
declare rolemembers scroll cursor for
  select memberuid, groupuid from Runtime80.dbo.sysmembers

declare @midFrom int, @midTo int, @member sysname
declare @gidFrom int, @gidTo int, @role sysname

open rolemembers
fetch next from rolemembers into @midFrom, @gidFrom

while @@FETCH_STATUS = 0
begin
   /* get the Runtime 90 memberid */
   select  @member = ruTo.name, @midTo = ruTo.uid 
      from Runtime.dbo.sysusers ruTo,
           Runtime80.dbo.sysusers ruFrom
       where ruTo.name = ruFrom.name
       and   ruFrom.uid = @midFrom 
   /* get the Runtime 90 groupid */
   select  @role = ruTo.name, @gidTo = ruTo.uid
      from Runtime.dbo.sysusers ruTo,
           Runtime80.dbo.sysusers ruFrom
       where ruTo.name = ruFrom.name
       and   ruFrom.uid = @gidFrom 
   /* insert in Runtime 90 sysmembers */
   if not exists
            (select memberuid, groupuid from Runtime.dbo.sysmembers
                     where memberuid = @midTo and groupuid = @gidTo)
            and @role is not NULL
            and @member is not NULL
   begin
      exec sp_addrolemember @role, @member
   end 
   
   fetch next from rolemembers into @midFrom, @gidFrom
end

close rolemembers
deallocate rolemembers

/* user Detail */
exec aaUserDetailUpdate

/* Update User Detail Access Level */
update UserDetail set AccessLevel = uFrom.AccessLevel   
   from Runtime80.dbo.UserDetail uFrom, Runtime.dbo.UserDetail uTo
     where uFrom.UserName = uTo.UserName
go

/* PrivateNameSpace */
BEGIN TRAN
/* This next step is necessary to allow Children to be inserted at the same time as the Parent */
alter table PrivateNameSpace disable trigger tI_PrivateNameSpace
set IDENTITY_INSERT PrivateNameSpace ON

insert into PrivateNameSpace (UserKey, NameKey, Type, ParentKey, Name, ConfigStor)
   select udTo.UserKey, pnsFrom.NameKey, pnsFrom.Type, pnsFrom.ParentKey, pnsFrom.Name, pnsFrom.ConfigStor
      from Runtime80.dbo.PrivateNameSpace pnsFrom, Runtime80.dbo.UserDetail udFrom, 
           Runtime.dbo.UserDetail udTo
         where udFrom.UserName = udTo.UserName
         and   udFrom.UserKey  = pnsFrom.UserKey
         and   pnsFrom.NameKey > 1  -- root key

set IDENTITY_INSERT PrivateNameSpace OFF
alter table PrivateNameSpace enable trigger tI_PrivateNameSpace
go
/* PrivateGroupTag */
insert into PrivateGroupTag (NameKey, UserKey, wwDomainTagKey)
   select pgtFrom.NameKey, udTo.UserKey, trTo.wwDomainTagKey
      from Runtime80.dbo.PrivateGroupTag pgtFrom, Runtime80.dbo.TagRef trFrom,
           Runtime80.dbo.UserDetail udFrom,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.TagRef trTo
         where udFrom.UserName = udTo.UserName
         and   udFrom.UserKey = pgtFrom.UserKey
         and   pgtFrom.wwDomainTagKey = trFrom.wwDomainTagKey
         and   trFrom.ServerKey = trTo.ServerKey
         and   trFrom.TagName = trTo.TagName
COMMIT TRAN
go

/* Annotation */
BEGIN TRAN
set IDENTITY_INSERT Annotation ON

insert into Annotation (AnnotationKey, UserKey, TagName, DateCreated,
                        Content, DateTime, Value)
   select aFrom.AnnotationKey, udTo.UserKey, aFrom.TagName, aFrom.DateCreated,
          aFrom.Content, aFrom.DateTime, aFrom.Value
      from Runtime80.dbo.Annotation aFrom, Runtime80.dbo.UserDetail udFrom,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.Tag tTo
         where udFrom.UserName = udTo.UserName
         and   udFrom.UserKey = aFrom.UserKey
         and   aFrom.TagName = tTo.TagName  -- Be sure the TagName is still there

set IDENTITY_INSERT Annotation OFF
COMMIT TRAN
go

/* WideTableDictionary */
BEGIN TRAN
insert into WideTableDictionary (UserKey, TagName)
   select udTo.UserKey, wtdFrom.TagName
      from Runtime80.dbo.WideTableDictionary wtdFrom, Runtime80.dbo.UserDetail udFrom,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.Tag tTo
         where udFrom.UserName = udTo.UserName
         and   udFrom.UserKey = wtdFrom.UserKey
         and   wtdFrom.TagName = tTo.TagName  -- Be sure the TagName is still there 
COMMIT TRAN
go


/* Summary Sub-System */
truncate table SummaryOperation
go
BEGIN TRAN
set IDENTITY_INSERT SummaryOperation ON

insert into SummaryOperation (OperationKey, TagName, CalcType, Description, Duration,
                              Resolution, TimeStamp, Frequency, SourceType)
   select OperationKey, TagName, CalcType, Description, Duration, Resolution,
          TimeStamp, Frequency, SourceType
      from Runtime80.dbo.SummaryOperation

set IDENTITY_INSERT SummaryOperation OFF
go
set IDENTITY_INSERT SummaryTagList ON

insert into SummaryTagList (SumVarKey, TagName, OperationKey, LowerLimit,
                            UpperLimit, Description)
   select SumVarKey, tTo.TagName, OperationKey, LowerLimit, UpperLimit, stlFrom.Description
      from Runtime80.dbo.SummaryTagList stlFrom, Runtime.dbo.Tag tTo
          where tTo.TagName = stlFrom.TagName

set IDENTITY_INSERT SummaryTagList OFF

COMMIT TRAN
go

/* Modification Tracking */
BEGIN TRAN
set IDENTITY_INSERT ModLogTable ON

insert into ModLogTable (ModTableKey, id, ModType, RowKey, UserKey, DateTime, UserName)
   select mFrom.ModTableKey, sysTo.id, mFrom.ModType, mFrom.RowKey, uTo.UserKey, mFrom.DateTime, uFrom.UserName
      from Runtime80.dbo.ModLogTable mFrom, Runtime80.dbo.InSQLSysObjects sysFrom,
           Runtime.dbo.HistorianSysObjects sysTo, Runtime80.dbo.UserDetail uFrom,
           Runtime.dbo.UserDetail uTo
         where sysTo.Name = sysFrom.Name
         and   uTo.UserName = uFrom.UserName
         and   mFrom.UserKey = uFrom.UserKey
         and   mFrom.id = sysFrom.id

set IDENTITY_INSERT ModLogTable OFF
go

/* If the ModLogTable contains entries for mods to the EngineeringUnit, Message or UserDetail tables,
   RowKey may need updating.  Do this for each of the tables.
*/
update ModLogTable set RowKey = euTo.EUKey
  from Runtime.dbo.ModLogTable mTo, Runtime.dbo.HistorianSysObjects sysTo,
       Runtime.dbo.EngineeringUnit euTo, Runtime80.dbo.EngineeringUnit euFrom
    where mTo.id = sysTo.id
	and sysTo.Name = 'EngineeringUnit'
	and euTo.Unit = euFrom.Unit

update ModLogTable set RowKey = msTo.MessageKey
  from Runtime.dbo.ModLogTable mTo, Runtime.dbo.HistorianSysObjects sysTo,
       Runtime.dbo.Message msTo, Runtime80.dbo.Message msFrom
    where mTo.id = sysTo.id
	and sysTo.Name = 'Message'
    and msFrom.Message0 + ' ' + msFrom.Message1 = msTo.Message0 + ' ' + msTo.Message1

update ModLogTable set RowKey = uTo.UserKey
  from Runtime.dbo.ModLogTable mTo, Runtime.dbo.HistorianSysObjects sysTo,
       Runtime.dbo.UserDetail uTo, Runtime80.dbo.UserDetail uFrom
    where mTo.id = sysTo.id
	and sysTo.Name = 'UserDetail'
	and uTo.UserName = uFrom.UserName


insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select ModTableKey, ColumnName, OldValue, NewValue
      from Runtime80.dbo.ModLogColumn mFrom

COMMIT TRAN
go

/* SystemParameter */
/* update just the user-configured rows */
/* Updates must be made one by one, hence we use a cursor here */
BEGIN TRAN
declare @spName nvarchar(50)
declare @spValue sql_variant

/* InSQL 9.0 does not have system parameter LogPath */
IF not exists (SELECT Name FROM SystemParameter WHERE Name = 'LogPath')
BEGIN 
    INSERT SystemParameter (Name, Value, Editable, Description)
        VALUES ('LogPath', 'c:\', 1, N'Error log path')
END

declare sysparam cursor for
  select Name, Value from Runtime80.dbo.SystemParameter
      where Editable = 1

open sysparam
fetch next from sysparam into @spName, @spValue
while @@FETCH_STATUS = 0
BEGIN
    update SystemParameter set Value = @spValue
	    where Name = @spName
    fetch next from sysparam into @spName, @spValue
END
close sysparam
deallocate sysparam

COMMIT TRAN
go

/* SQLTemplate table                 */
/* Add templates created by the user */
BEGIN TRAN

insert into SQLTemplate (Description, Type, Script)
   select Description, Type, Script 
       from Runtime80.dbo.SQLTemplate
           where TemplateKey > 10

COMMIT TRAN
go
           
/* Clean up the Status columns and Config table */
BEGIN TRAN
update Tag set Status = 0
update Topic set Status = 0
update IOServer set Status = 0
update IODriver set Status = 0
update StorageLocation set Status = 0
update SnapshotDetail set Status = 0
update SystemParameter set Status = 0
COMMIT TRAN
go

truncate table ConfigStatusPending
go

/* Shrink the db */
use master
go
BACKUP LOG Runtime with TRUNCATE_ONLY
go
DBCC SHRINKDATABASE (Runtime, 20)
go


