logo
Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Options
Go to last post Go to first unread
bart  
#1 Posted : Tuesday, January 24, 2012 3:38:48 PM(UTC)
bart

Rank: Administration

Reputation:

Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188
Man

Was thanked: 1 time(s) in 1 post(s)
Sometimes for some reason you want to change the CID of a website.
I made some sql script which can make this happen for you.
however, it worked on the site I tested it on, SEO isn't included jet, I might at this at a later time.
And also stuff which isn't used in this site isn't added.

Here is My query changing CID ES to EYESALE
Those parameters is set, but in the query you have to change some tablenames:
enjoy;)
Code:
BEGIN TRANSACTION
BEGIN TRY
Declare @NewCid nvarchar(100) = 'Eyesale'
Declare @OldCid nvarchar(100) = 'ES'

if (Select COUNT(*) from pbw_cid where Cid = @NewCid) = 0
begin
  Update pbw_cid
  Set Cid= @NewCid
  ,UpdateWebConfig = 'Ja'
  ,UpdateIIS = 'Ja'
  where Cid = @OldCid

  Update [PBW_Cid_Adm_Toegang]
  Set Cid= @NewCid
  where Cid = @OldCid

  Update [PBW_Packages_Jobs]
  Set Cid= @NewCid
  where Cid = @OldCid
  
  Update [PBW_Gebruikers]
  Set Cid= @NewCid
  where Cid = @OldCid
  
  Update [PBW_CID_Websites]
  Set Cid= @NewCid
  where Cid = @OldCid
  
  Update [PBW_Support_Meldingen]
  Set Cid= @NewCid
  where Cid = @OldCid  
  
  update [ES_CONFIG]
  set WAARDE = Replace(Cast(waarde as nvarchar(max)),@OldCid + '_',@NewCid + '_')
  update [ES_CONFIG]
  set WAARDE = Replace(Cast(waarde as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  update [ES_FORMULIEREN]
  set [VERVOLGURL] = Replace(Cast([VERVOLGURL] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  update [ES_MENU]
  set [QUERYOVERRULE] = Replace(Cast([QUERYOVERRULE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_') 
  ,[QUERYSTRING] = Replace(Cast([QUERYSTRING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_') 
  ,[ORDERBYOVERRULE] = Replace(Cast([ORDERBYOVERRULE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_') 
  ,[ORDERBYSTRING] = Replace(Cast([ORDERBYSTRING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_') 
  
  update [ES_TEKSTEN]
  set [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  
  update [ES_VIEW]
  set [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[ZOEKLAYOUT] = Replace(Cast([ZOEKLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[KOPLAYOUT] = Replace(Cast([KOPLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[VOETLAYOUT] = Replace(Cast([VOETLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[SUBKOPLAYOUT] = Replace(Cast([SUBKOPLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[SUBVOETLAYOUT] = Replace(Cast([SUBVOETLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[TEKSTEVEN] = Replace(Cast([TEKSTEVEN] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[SORTERING] = Replace(Cast([SORTERING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  ,[SELECTIE] = Replace(Cast([SELECTIE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')

  Update [ES_menu]
  SET [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
  Where [Type] = 'EXTERN'

  update [ES_TABEL_VELDEN]
  set KOPPELQUERY = REPLACE(KOPPELQUERY, @OldCid + 'U_',@NewCid + 'U_')

  update [ES_SEO]
  set [DestinationUrlTemplate] = Replace(Cast([DestinationUrlTemplate] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
      
  DECLARE @TABELNAAM varchar(500)
  DECLARE table_cursor CURSOR FOR
  SELECT [TABELNAAM] FROM ES_TABEL
  OPEN table_cursor
  FETCH NEXT FROM table_cursor
  into @TABELNAAM
  WHILE @@FETCH_STATUS = 0
  BEGIN
     declare @oldtablename nvarchar(max)= @OldCid + 'U_' + @TABELNAAM
     declare @Newtablename nvarchar(max)= @NewCid + 'U_' + @TABELNAAM
     exec sp_rename @oldtablename, @Newtablename
     FETCH NEXT FROM table_cursor
     into @TABELNAAM
  END
  CLOSE table_cursor
  DEALLOCATE table_cursor
  
  declare @oldtablenameSystem nvarchar(max)= @OldCid + '_CONFIG'
  declare @NewtablenameSystem nvarchar(max)= @NewCid + '_CONFIG'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_DOWNLOAD'
  set @NewtablenameSystem = @NewCid + '_DOWNLOAD'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_FORMULIEREN'
  set @NewtablenameSystem = @NewCid + '_FORMULIEREN'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_FOTO'
  set @NewtablenameSystem = @NewCid + '_FOTO'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_GROEPEN'
  set @NewtablenameSystem = @NewCid + '_GROEPEN'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_MAILING'
  set @NewtablenameSystem = @NewCid + '_MAILING'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_MENU'
  set @NewtablenameSystem = @NewCid + '_MENU'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_PMatrix'
  set @NewtablenameSystem = @NewCid + '_PMatrix'
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
  begin
    exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  end
  set @oldtablenameSystem = @OldCid + '_SEO'
  set @NewtablenameSystem = @NewCid + '_SEO'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_SEOHISTORY'
  set @NewtablenameSystem = @NewCid + '_SEOHISTORY'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_TABEL'
  set @NewtablenameSystem = @NewCid + '_TABEL'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_TABEL_VELDEN'
  set @NewtablenameSystem = @NewCid + '_TABEL_VELDEN'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_TEKSTEN'
  set @NewtablenameSystem = @NewCid + '_TEKSTEN'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_TOEGANG'
  set @NewtablenameSystem = @NewCid + '_TOEGANG'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_VIEW'
  set @NewtablenameSystem = @NewCid + '_VIEW'
  exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  set @oldtablenameSystem = @OldCid + '_TABEL_INDEX'
  set @NewtablenameSystem = @NewCid + '_TABEL_INDEX'
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
  begin
    exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  end
  set @oldtablenameSystem = @OldCid + '_TABEL_INDEX_VELD'
  set @NewtablenameSystem = @NewCid + '_TABEL_INDEX_VELD'
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
  begin
    exec sp_rename @oldtablenameSystem, @NewtablenameSystem
  end
end

COMMIT TRANSACTION
  select 'success'
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage
END CATCH


EDIT: 1. for the user-tables you need a right trim because else you get black spaces in your tablename
2. I think it's better to uppercase the New CID before starting
I might will update the sql script later but first I need to have a CID to rename so I can test it.

Edited by user Tuesday, May 29, 2018 9:03:49 AM(UTC)  | Reason: Not specified

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

bart  
#2 Posted : Thursday, July 7, 2016 3:15:46 PM(UTC)
bart

Rank: Administration

Reputation:

Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188
Man

Was thanked: 1 time(s) in 1 post(s)
this field should still be updaten
Code:
  update [SQ_TABEL_VELDEN]
  set KOPPELQUERY = REPLACE(KOPPELQUERY,'UKHSU_','SQU_')
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.