This script will need to be edited to be customized for the servers.
USE MANITOU
-------------------------------------------------------------
/*VARIABLES - DO NOT EDIT*/
-------------------------------------------------------------
DECLARE @OLDAPPSYSNO int
DECLARE @NEWAPPSYSNO int
DECLARE @NEWAPPSYSDESC varchar(35)
DECLARE @OLDAPPDEVICENO int
DECLARE @NEWAPPDEVICENO int
DECLARE @NEWAPPMACHINENAME varchar(15)
DECLARE @NEWAPPDEVICEIP1 varchar(15)
DECLARE @NEWAPPDEVICEIP2 varchar(15)
DECLARE @NEWDBMACHINENAME varchar(15)
DECLARE @NEWDBDEVICEIP1 varchar(15)
DECLARE @NEWDBDEVICEIP2 varchar(15)
DECLARE @NEWDBDEVICENO int
DECLARE @USEEXISTING bit
-------------------------------------------------------------
/* USER NEEDED INPUT */
-------------------------------------------------------------
--Enter System Number of Already existing System Configuration
SET @OLDAPPSYSNO = '1'
--Enter New System Number for new SYSTEM Configuration
SET @NEWAPPSYSNO = '2'
--Enter New System Configuration Name
Set @NEWAPPSYSDESC = 'System 2'
--Enter Device Numbers that need to be copied
SET @OLDAPPDEVICENO = '1'
--Enter New Device Numbers
SET @NEWAPPDEVICENO = '2'
--Enter New Machine Name
SET @NEWAPPMACHINENAME = 'MANITOU02'
--Enter New Machine IP
SET @NEWAPPDEVICEIP1 = '127.0.0.1'
--Enter New Machine IP Secondary (If Exists) KEEP AS NULL UNLESS DATA EXISTS
SET @NEWAPPDEVICEIP2 = 'NULL'
--USE THIS SECTION IF Database is on a seperate server **********************************************************************
--Enter New Device Number for the Database Server
SET @NEWDBDEVICENO = '3'
--Enter New DB server Name (If this field is null, the DB server will not change from the app server machine)
SET @NEWDBMACHINENAME = 'NULL'
--Enter New DB Server IP
SET @NEWDBDEVICEIP1 = '127.0.0.1'
--Enter New DB Server IP Secondary (If Exists) KEEP AS NULL UNLESS DATA EXISTS
SET @NEWDBDEVICEIP2 = 'NULL'
/****************************************************************************************************************************/
-------------------------------------------------------------
/* MAIN SCRIPT - DO NO EDIT */
-------------------------------------------------------------
--Error Checking
IF exists (SELECT * FROM SYSCONF WHERE SYSCONF = @NEWAPPSYSNO)
BEGIN
raiserror('The System Configuration number you are trying to add already exists.', 1, 1)
GOTO sqlend
END
ELSE
print 'No System Configuration Number Conflicts In SYSCONF.'
IF exists (SELECT * FROM DEVCONF_D WHERE SYSCONF = @NEWAPPSYSNO)
BEGIN
raiserror('The device configuration already contains system information for the system you are trying to add.', 1, 1)
GOTO sqlend
END
ELSE
print 'No System Configuration Number Conflicts In DEVCONF_D'
IF exists (SELECT * FROM DEVCONF_D WHERE DEVICE = @NEWAPPDEVICENO)
BEGIN
raiserror('The device configuration already contains information for the new device you are trying to add.', 1, 1)
GOTO sqlend
END
ELSE
print 'No Device Number Conflicts In DEVCONF_D'
IF exists (SELECT * FROM DEVCONF WHERE IPADDR = @NEWAPPDEVICEIP1)
BEGIN
raiserror('The device list already contains a server with the first IP address specified.', 1, 1)
GOTO sqlend
END
ELSE
print 'No Primary IP Address Conflicts in DEVCONF'
IF exists (SELECT * FROM DEVCONF WHERE IPADDR2 = @NEWAPPDEVICEIP2)
BEGIN
raiserror('The device list already contains a server with the second IP address specified.', 1, 1)
GOTO sqlend
END
ELSE
print 'No Secondary IP Address Conflicts in DEVCONF'
IF exists (SELECT * FROM DEVCONF WHERE Name = @NEWAPPMACHINENAME)
BEGIN
raiserror('The device list already contains a server with the machine name specified.', 1, 1)
GOTO sqlend
END
ELSE
print 'No Machine Name Conflicts in DEVCONF'
--DB stuff
IF @NEWDBMACHINENAME <> 'Null'
BEGIN
SET @USEEXISTING = 0
IF exists (SELECT *
FROM DEVCONF
WHERE DEVICE = @NEWDBDEVICENO
and (IPADDR != @NEWDBDEVICEIP1
or (IPADDR2 != @NEWDBDEVICEIP2 and @NEWDBDEVICEIP2 <> 'NULL')
or NAME != @NEWDBMACHINENAME)
)
BEGIN
raiserror('The device list already contains a different server on that device number for the db server specified', 1, 1)
GOTO sqlend
END
ELSE
print 'New Device Entry for the DB Server is valid.'
IF exists (SELECT * FROM DEVCONF WHERE DEVICE = @NEWDBDEVICENO and IPADDR = @NEWDBDEVICEIP1 and NAME = @NEWDBMACHINENAME)
SET @USEEXISTING = 1
END
--Create separate table for altering data for device configuration
SELECT * INTO DEVCONF_D_NEW FROM DEVCONF_D
WHERE SYSCONF = @OLDAPPSYSNO;
print 'DEVCONF_D_NEW created'
--create new Device Configuration
UPDATE DEVCONF_D_NEW SET DEVICE = @NEWAPPDEVICENO WHERE DEVICE = @OLDAPPDEVICENO;
UPDATE DEVCONF_D_NEW SET SYSCONF = @NEWAPPSYSNO WHERE SYSCONF = @OLDAPPSYSNO;
--DB entry is already updated with the correct sysconf just move the device number to our new db server
IF @NEWDBMACHINENAME != 'Null'
UPDATE DEVCONF_D_NEW SET DEVICE = @NEWDBDEVICENO WHERE APPTYPE = 13
--copy new data back into current DEVCONF
INSERT INTO DEVCONF_D
SELECT * FROM DEVCONF_D_NEW WHERE SYSCONF = @NEWAPPSYSNO;
--Drop the separate table made for altering data
DROP TABLE DEVCONF_D_NEW
print 'DEVCONF_D_NEW dropped'
--Create separate table for altering data for System configuration
SELECT * INTO SYSCONF_NEW FROM SYSCONF
WHERE SYSCONF = @OLDAPPSYSNO;
print 'SYSCONF_NEW created'
--create new System Configuration
UPDATE SYSCONF_NEW SET SYSCONF = @NEWAPPSYSNO WHERE SYSCONF = @OLDAPPSYSNO;
UPDATE SYSCONF_NEW SET DESCR = @NEWAPPSYSDESC WHERE SYSCONF = @NEWAPPSYSNO;
--copy new data back into current DEVCONF
INSERT INTO SYSCONF
SELECT * FROM SYSCONF_NEW WHERE SYSCONF = @NEWAPPSYSNO;
--Drop the separate table made for altering data
DROP TABLE SYSCONF_NEW
print 'SYSCONF_NEW dropped'
--Create the new Device/MACHINE
SELECT * INTO DEVCONF_NEW FROM DEVCONF
WHERE DEVICE = @OLDAPPDEVICENO;
print 'DEVCONF_NEW created'
--create new System Configuration
UPDATE DEVCONF_NEW SET DEVICE = @NEWAPPDEVICENO WHERE DEVICE = @OLDAPPDEVICENO;
UPDATE DEVCONF_NEW SET NAME = UPPER(@NEWAPPMACHINENAME) WHERE DEVICE = @NEWAPPDEVICENO;
UPDATE DEVCONF_NEW SET IPADDR = @NEWAPPDEVICEIP1 WHERE DEVICE = @NEWAPPDEVICENO;
IF @NEWAPPDEVICEIP2 != 'NULL' UPDATE DEVCONF_NEW SET IPADDR2 = @NEWAPPDEVICEIP2 WHERE DEVICE = @NEWAPPDEVICENO;
IF @NEWAPPDEVICEIP2 = 'NULL' UPDATE DEVCONF_NEW SET IPADDR2 = NULL WHERE DEVICE = @NEWAPPDEVICENO;
IF @NEWDBMACHINENAME != 'NULL' and @USEEXISTING = 0
INSERT INTO DEVCONF_NEW (LASTUPD, CREDATE, DEVICE, [NAME], IPADDR, IPADDR2, [DISABLED])
SELECT GETDATE(), GETDATE(), @NEWDBDEVICENO, @NEWDBMACHINENAME, @NEWDBDEVICEIP1,
CASE WHEN @NEWDBDEVICEIP2 = 'NULL' THEN null ELSE @NEWDBDEVICEIP2 END, 0 'DISABLED'
--copy new data back into current DEVCONF
INSERT INTO DEVCONF
SELECT * FROM DEVCONF_NEW WHERE DEVICE = @NEWAPPDEVICENO;
IF @NEWDBMACHINENAME != 'NULL'
INSERT INTO DEVCONF
SELECT * FROM DEVCONF_NEW WHERE DEVICE = @NEWDBDEVICENO;
--Drop the separate table made for altering data
DROP TABLE DEVCONF_NEW
print 'DEVCONF_NEW dropped'
--Shortcut to end sql if error occurs.
sqlend:
