Page Comparison

...

1/ Launch SQL Server Management Studio.

2/ Click on "New query".

3/ Copy and paste the script below in SQL Server Management Studio and replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database.

Code Block

language	sql
title	Copy the following text into the opened tab:
linenumbers	true
collapse	true

declare @dbName nvarchar(MAX);
set @dbName = 'DATAWAREHOUSE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database

execute ('CREATE DATABASE ' + @dbName);

execute ('use ' + @dbName + '; ' +
'create table dbo.IC_D_ROUTE (
ID varchar(255) not null,
CODE varchar(255),
EXTERNAL_ID varchar(255),
ROUTE_DATE date,
DOMAIN_CODE varchar(255),
LOGISTICS_STATUS varchar(255),
TRUCK_CODE varchar(255),
DRIVER_CODE varchar(255),
TRAILER_CODE varchar(255),
primary key(ID)
);


create table dbo.IC_M_ROUTE (
ID_ROUTE varchar(255) not null,
TOTAL_DISTANCE_PREDICTED numeric(19,6),
TOTAL_DISTANCE numeric(19,6),
NOMINAL_DIFF_TOTAL_DISTANCE numeric(19,6),
RELATIVE_DIFF_TOTAL_DISTANCE numeric(19,6),
TOTAL_TIME_PREDICTED numeric(19,6),
TOTAL_TIME numeric(19,6),
NOMINAL_DIFF_TOTAL_TIME numeric(19,6),
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_DELIVERED_PREDICTED numeric(19,6),
UV_DELIVERED numeric(19,6),
NOMINAL_DIFF_UV_DELIVERED numeric(19,6),
RELATIVE_DIFF_UV_DELIVERED numeric(19,6),
UP_DELIVERED_PREDICTED numeric(19,6),
UP_DELIVERED numeric(19,6),
NOMINAL_DIFF_UP_DELIVERED numeric(19,6),
RELATIVE_DIFF_UP_DELIVERED numeric(19,6),
UQ_DELIVERED_PREDICTED numeric(19,6),
UQ_DELIVERED numeric(19,6),
NOMINAL_DIFF_UQ_DELIVERED numeric(19,6),
RELATIVE_DIFF_UQ_DELIVERED numeric(19,6),
UM_DELIVERED_PREDICTED numeric(19,6),
UM_DELIVERED numeric(19,6),
NOMINAL_DIFF_UM_DELIVERED numeric(19,6),
RELATIVE_DIFF_UM_DELIVERED numeric(19,6),
UV_COLLECTED_PREDICTED numeric(19,6),
UV_COLLECTED numeric(19,6),
NOMINAL_DIFF_UV_COLLECTED numeric(19,6),
RELATIVE_DIFF_UV_COLLECTED numeric(19,6),
UP_COLLECTED_PREDICTED numeric(19,6),
UP_COLLECTED numeric(19,6),
NOMINAL_DIFF_UP_COLLECTED numeric(19,6),
RELATIVE_DIFF_UP_COLLECTED numeric(19,6),
UQ_COLLECTED_PREDICTED numeric(19,6),
UQ_COLLECTED numeric(19,6),
NOMINAL_DIFF_UQ_COLLECTED numeric(19,6),
RELATIVE_DIFF_UQ_COLLECTED numeric(19,6),
UM_COLLECTED_PREDICTED numeric(19,6),
UM_COLLECTED numeric(19,6),
NOMINAL_DIFF_UM_COLLECTED numeric(19,6),
RELATIVE_DIFF_UM_COLLECTED numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
END_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
FILLRATE_UV numeric(19,6),
FILLRATE_UV_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UV numeric(19,6),
RELATIVE_DIFF_FILLRATE_UV numeric(19,6),
FILLRATE_UP numeric(19,6),
FILLRATE_UP_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UP numeric(19,6),
RELATIVE_DIFF_FILLRATE_UP numeric(19,6),
#SITES_DELIVERED int,
#SITES_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#SITES_DELIVERED int,
RELATIVE_DIFF_#SITES_DELIVERED numeric(19,6),
#SITES_COLLECTED int,
#SITES_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#SITES_COLLECTED int,
RELATIVE_DIFF_#SITES_COLLECTED numeric(19,6),
#OPERATIONS_DELIVERED int,
#OPERATIONS_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_DELIVERED int,
RELATIVE_DIFF_#OPERATIONS_DELIVERED numeric(19,6),
#OPERATIONS_COLLECTED int,
#OPERATIONS_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_COLLECTED int,
RELATIVE_DIFF_#OPERATIONS_COLLECTED numeric(19,6),
TRANSPORTATION_COST_PREDICTED numeric(19,6),
TRANSPORTATION_COST numeric(19,6),
NOMINAL_DIFF_TRANSPORTATION_COST numeric(19,6),
RELATIVE_DIFF_TRANSPORTATION_COST numeric(19,6),
primary key(ID_ROUTE)
);


create table dbo.IC_D_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
ROUTE_ORDER int,
STARTING_SITE_CODE varchar(255),
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);


create table dbo.IC_D_OPERATION (
ID varchar(255) not null,
DIVISION_INDEX int not null,
ID_ROUTE varchar(255),
CLIENT_SITE_CODE varchar(255),
DEPOSIT_SITE_CODE varchar(255),
PRODUCT_CODE varchar(255),
PACKAGING_CODE varchar(255),
CLIENT_SEQ_ACTIVITY_CODE varchar(255),
CLIENT_SEQ_SITE_CODE varchar(255),
CLIENT_SEQ_PASSAGE_INDEX int,
DEPOSIT_SEQ_ACTIVITY_CODE varchar(255),
DEPOSIT_SEQ_SITE_CODE varchar(255),
DEPOSIT_SEQ_PASSAGE_INDEX int,
primary key(ID, DIVISION_INDEX)
);


create table dbo.IC_M_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
TOTAL_TIME_PREDICTED int,
TOTAL_TIME int,
NOMINAL_DIFF_TOTAL_TIME int,
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_PREDICTED numeric(19,6),
UV numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
END_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);


create table dbo.IC_M_OPERATION (
ID_OPERATION varchar(255) not null,
DIVISION_INDEX int not null,
UV_PREDICTED numeric(19,6),
UV_INITIAL numeric(19,6),
UV_REALISED numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP_INITIAL numeric(19,6),
UP_REALISED numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ_INITIAL numeric(19,6),
UQ_REALISED numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM_INITIAL numeric(19,6),
UM_REALISED numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6), 
primary key(ID_OPERATION, DIVISION_INDEX)
);

create table dbo.IC_D_LOGISTIC_AREA (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_SITE (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_PRODUCT (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_PROVIDER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_TRUCK (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_DRIVER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_TRAILER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''', 
DEFAULT_DATABASE=[' + @dbName + '], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 

ALTER LOGIN [' + @dbName + '] ENABLE

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + ']

ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + ']

CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + ' 

ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo]

EXEC sp_addrolemember ''db_owner'', ''' + @dbName + '''

USE [master]

GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa

RECONFIGURE ; 

USE [' + @dbName + ']

ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + ']

RECONFIGURE ; 
');

4/ Click on "Execute". You should have a confirmation message "Command(s) completed successfully".

...

1/ Always in SQL Server Management Studio, click again on "New query" to open a new request tab.

2/ Copy and paste the script below in SQL Server Management Studio and replace SPAGOBI_DATABASE_NAME by the desired name for your datawarehouse database.

Code Block

title	Copy the following text into the opened tab:
linenumbers	true
collapse	true

declare @dbName nvarchar(MAX);
set @dbName = 'SPAGOBI_DATABASE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database

/** Create the Database **/
execute ( '
	CREATE DATABASE [' + @dbName + ']

')

/** Compatibility SQLServer 2008 100 **/
/** Compatibility SQLServer 2012 110 **/
/** Compatibility SQLServer 2014 120 **/
/** Compatibility SQLServer 2016 130 **/
/** Compatibility SQLServer 2017 140 **/
execute ( '
	ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = 110

	ALTER DATABASE [' + @dbName + '] SET ALLOW_SNAPSHOT_ISOLATION ON 

')

/****** Create the Login [' + @dbName + '] ******/
execute ( '
	CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''', 
	DEFAULT_DATABASE=[' + @dbName + '], 
	DEFAULT_LANGUAGE=[us_english], 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF

	ALTER LOGIN [' + @dbName + '] ENABLE

')

/** Add the required roles for the created login **/
execute ( '
	ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + ']

	ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + ']

')

execute ( '
	CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + ' 

	ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo]

')

/** Give the created login the ownership and connect rights on the created database **/
execute ( '
	EXEC sp_addrolemember ''db_owner'', ''' + @dbName + '''

	GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa

	RECONFIGURE ;  

')

/** Give the created database a user linked to the created login and set it as the owner **/
execute ( '
	USE [' + @dbName + ']

	CREATE USER [' + @dbName + '] FOR LOGIN [' + @dbName + ']

	ALTER USER [' + @dbName + '] WITH DEFAULT_SCHEMA=[dbo]

	ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + ']

	RECONFIGURE ;  

')

3/ Click on "Execute". You should have a confirmation message "Command(s) completed successfully".

...

Version	Old Version 53	New Version 54
Changes made by	Axel PHILIPPON-LEBREC (Unlicensed)	Axel PHILIPPON-LEBREC (Unlicensed)
Saved on	May 23, 2018	May 23, 2018

Versions Compared

Key