Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor        : J.Feuerstein'
Print ' Description  : Renames From in JMDb from SiteID Old to SiteID New       '
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

DECLARE @RenamedCounter INT
DECLARE @RenameN INT
SET @RenameN = 0
SET @RenamedCounter = 0

declare @Id  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @SiteID_Old nvarchar(32),
        @SiteID_Old_ObjectFromSiteID nvarchar(32),
        @SiteID_Old_ObjectOwningSiteID nvarchar(32),
        @SiteID_NEW nvarchar(32)

set @ObjectCliName=''
set @SiteID_Old='#SiteID_Old#'
set @SiteID_NEW='#SiteID_New#'
select @RenameN=count(*)
from dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectFromSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectOwningSiteID = @SiteID_Old

print('.')
Print '-------------------------------------------------------------------------'
print('Rename in DB.....: ' + '#DB_N#')
print('from SiteID_Old..: [' + @SiteID_Old + ']')
print('to   SiteID_New..: [' + @SiteID_NEW + ']')
print('N to Rename......: [' + CONVERT(nvarchar(64),@RenameN) + ']')
Print '-------------------------------------------------------------------------'

-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare zztmpClean2_tbl_Cursor cursor fast_forward for
select Id, ObjectCliName, ObjectFromSiteID, ObjectOwningSiteID
from dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectFromSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectOwningSiteID = @SiteID_Old


set nocount on
open zztmpClean2_tbl_Cursor
 -- alle Objekte durchlaufen die durch den Cursor durchlaufen
 while 1=1
  begin
    SET @RenamedCounter = @RenamedCounter + 1
    fetch next from zztmpClean2_tbl_Cursor into @Id, @ObjectCliName, @SiteID_Old_ObjectFromSiteID, @SiteID_Old_ObjectOwningSiteID
    -- if (@RenamedCounter >=10) break
    -- Ausstieg wenn letztes Element
    if (@@fetch_status <> 0) break

    print('Rename:[' + CONVERT(nvarchar(64),@RenamedCounter) + '/' + CONVERT(nvarchar(64),@RenameN) + ':' + Str((100.00*@RenamedCounter/@RenameN),6,2)  + '%]' +
          ' Cli: [' + @ObjectCliName + ']' +
          ' F.SiteID: [' +  @SiteID_Old_ObjectFromSiteID + ']' +
          ' O.SiteID: [' +  @SiteID_Old_ObjectOwningSiteID + ']'
          )
     if upper(@SiteID_Old_ObjectFromSiteID) = upper(@SiteID_Old)
     begin
       print (' |-> Rename:' + ' F.SiteID: [' +  @SiteID_Old_ObjectFromSiteID + '] to :' + @SiteID_New )
       UPDATE Objects_tbl SET ObjectFromSiteID = @SiteID_NEW  where ID=@ID
     end

     if upper(@SiteID_Old_ObjectOwningSiteID) = upper(@SiteID_Old)
     begin
       print (' |-> Rename:' + ' O.SiteID: [' +  @SiteID_Old_ObjectOwningSiteID + '] to :' + @SiteID_New )
       UPDATE Objects_tbl SET ObjectOwningSiteID = @SiteID_NEW  where ID=@ID
     end

     -- 10.05.2013 07:19 J.Fes #new to update also ProcessOnSiteID for Process Results
     UPDATE JobControl_ObjectsProcessData_tbl
        SET JobControl_ObjectsProcessData_tbl.ProcessOnSiteID=@SiteID_NEW
        where JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @ID



  end
close zztmpClean2_tbl_Cursor
deallocate zztmpClean2_tbl_Cursor



Print ''
Print '-------------------------------------------------------------------------'
Print ' Script executed'
Print '-------------------------------------------------------------------------'
GO
