Check metadata db

Regedit
Computer\HKEY_LOCAL_MACHINE\Wow6432Node\MicroStrategy\Data Sources\CastorServer
Key: name-Location, type-REG_SZ, data-DSN=MSTRMD;

Service manager > tools > odbc32 or
Admin tools > odbc32
Sql server – Server1\mstr_pma_pt
Database- MSTRV941

Create database MSTRV941
Configuration Wizard > Create Metadata
C:\Program Files (x86)\Common Files\MicroStrategy\mdsql.sql

MSTR having metadata in different machine
Service account (domain1\service_account) should be in machine admin group.
Control panel > user accounts > manage user accounts > add service account to admin group


mdsql.sql
C:\Program Files (x86)\Common Files\MicroStrategy\mdsql.sql

-- MicroStrategy 9.3 Metadata Creation Script
--  for Microsoft SQL Server, version 2000 and higher
-- Copyright (C) 1997-2012 MicroStrategy, Inc. All Rights Reserved.
-- This script has following sections:
-- @@0                8.x metadata table schema with latest column definition.
-- @@UNICODE        convert existing 8.x schema metadatas for use in any database regardless of character set encoding.
-- @@1                9.0 introduced metadata tables, and it includes NCS tables creation as well.
-- @@2                9.0.1 introduced metadata tables.
-- @@SECU        remove duplicate records in DSSMDOBJSECU table.
-- @@4                9.0.2 introduced metadata tables.
-- @@5                9.2.0 introduced metadata tables.
-- @@6                It is ONLY used to UPDATE an existing old schema MD, and will change GUID storage format from CHAR(32) to UNIQUEIDENTIFIER.
-- @@8                9.3.1 introduced metadata tables.
-- @@9                9.3.1 add two new columns to DSSMDUSRACCT.
-- @@FINAL        All the tasks should be done after schema change finishes is put in this section.
 
 
-- @@0
 
-- Drop existing repository
DROP TABLE DSSMDSYSPROP;
DROP TABLE DSSMDOBJDEFN;
DROP TABLE DSSMDOBJDEPN;
DROP TABLE DSSMDOBJPROP;
DROP TABLE DSSMDOBJCMNT;
DROP TABLE DSSMDOBJSECU;
DROP TABLE DSSMDUSRACCT;
DROP TABLE DSSMDOBJINFO;
DROP TABLE DSSMDLNKITEM;
DROP TABLE DSSMDLNKPROP;
 
-- Create DSSMDSYSPROP table
CREATE TABLE DSSMDSYSPROP
(
    NAME                    VARCHAR(250) NOT NULL,
    PROP_VAL                VARCHAR(250) NULL
);
 
ALTER TABLE DSSMDSYSPROP ADD CONSTRAINT PK_SYSPROP PRIMARY KEY (NAME);
 
-- Create DSSMDOBJINFO table
CREATE TABLE DSSMDOBJINFO
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    OBJECT_TYPE             INT NOT NULL,
    SUBTYPE                 INT NOT NULL,
    OBJECT_NAME             NVARCHAR(250) NOT NULL,
    ABBREVIATION            NVARCHAR(250) NULL,
    DESCRIPTION             NVARCHAR(250) NULL,
    VERSION_ID             UNIQUEIDENTIFIER NOT NULL,
    PARENT_ID              UNIQUEIDENTIFIER NOT NULL,
    OWNER_ID               UNIQUEIDENTIFIER NOT NULL,
    HIDDEN                  INT NOT NULL,
    CREATE_TIME             DATETIME NULL,
    MOD_TIME                DATETIME NULL,
    OBJECT_UNAME            NVARCHAR(250) NOT NULL,
    OBJECT_STATE            INT NOT NULL,
    LOCALE                        INT DEFAULT 0 NOT NULL,
    EXTENDED_TYPE                INT DEFAULT 0 NOT NULL,
    VIEW_MEDIA                        INT DEFAULT 0 NOT NULL,
    ICON_PATH                        NVARCHAR(250) DEFAULT ''
);
 
ALTER TABLE DSSMDOBJINFO ADD CONSTRAINT PK_OBJINFO PRIMARY KEY (PROJECT_ID, OBJECT_ID);
 
CREATE INDEX IX_OBJINFO_OBJECT ON DSSMDOBJINFO (OBJECT_ID);
CREATE INDEX IX_OBJINFO_PARENT ON DSSMDOBJINFO (PARENT_ID);
CREATE INDEX IX_OBJINFO_TYPE ON DSSMDOBJINFO (OBJECT_TYPE);
CREATE INDEX IX_OBJINFO_SUBTYPE ON DSSMDOBJINFO (SUBTYPE);
CREATE INDEX IX_OBJINFO_UNAME ON DSSMDOBJINFO (OBJECT_UNAME);
 
-- Create DSSMDOBJDEFN table
CREATE TABLE DSSMDOBJDEFN
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    DEFINITION_SEQ          INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL,
    DEFINITION              NVARCHAR(2000) NOT NULL
);
 
ALTER TABLE DSSMDOBJDEFN ADD CONSTRAINT PK_OBJDEFN PRIMARY KEY (PROJECT_ID, OBJECT_ID, DEFINITION_SEQ) ;
 
CREATE INDEX IX_OBJDEFN_OBJECT ON DSSMDOBJDEFN (OBJECT_ID);
 
-- Create DSSMDOBJDEPN table
CREATE TABLE DSSMDOBJDEPN
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    DEPN_PRJID             UNIQUEIDENTIFIER NOT NULL,
    DEPN_OBJID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_TYPE             INT NOT NULL,
    DEPNOBJ_TYPE            INT NOT NULL
);
 
ALTER TABLE DSSMDOBJDEPN ADD CONSTRAINT PK_OBJDEPN PRIMARY KEY (PROJECT_ID, OBJECT_ID, DEPN_PRJID, DEPN_OBJID);
 
CREATE INDEX IX_OBJDEPN_OBJECT ON DSSMDOBJDEPN (OBJECT_ID);
CREATE INDEX IX_OBJDEPN_DEPN ON DSSMDOBJDEPN (DEPN_OBJID, DEPN_PRJID);
CREATE INDEX IX_OBJDEPN_TYPE ON DSSMDOBJDEPN (OBJECT_TYPE);
CREATE INDEX IX_OBJDEPN_DEPTYPE ON DSSMDOBJDEPN (DEPNOBJ_TYPE);
 
-- Create DSSMDOBJPROP table
CREATE TABLE DSSMDOBJPROP
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    PROPSET_ID             UNIQUEIDENTIFIER NOT NULL,
    PROP_ID                 INT NOT NULL,
    VALUE_SEQ               INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL,
    PROP_VAL                NVARCHAR(2000) NULL,
    TARGET_ID              UNIQUEIDENTIFIER NOT NULL,
    PROP_TYPE               SMALLINT NOT NULL
);
 
ALTER TABLE DSSMDOBJPROP ADD CONSTRAINT PK_OBJPROP PRIMARY KEY (PROJECT_ID, OBJECT_ID, PROPSET_ID, PROP_ID, VALUE_SEQ) ;
CREATE INDEX IX_OBJPROP_OBJECT ON DSSMDOBJPROP (OBJECT_ID);
 
-- Create DSSMDOBJCMNT table
CREATE TABLE DSSMDOBJCMNT
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    COMMENT_ID              INT NOT NULL,
    COMMENT_SEQ             INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL,
    COMMENT_VAL             NVARCHAR(2000) NULL,
    LOCALE                  INT DEFAULT 0 NOT NULL
);
 
ALTER TABLE DSSMDOBJCMNT ADD CONSTRAINT PK_OBJCMNT PRIMARY KEY (PROJECT_ID, OBJECT_ID, COMMENT_ID, LOCALE, COMMENT_SEQ);
CREATE INDEX IX_OBJCMNT_OBJECT ON DSSMDOBJCMNT (OBJECT_ID);
 
-- Create DSSMDOBJSECU table
CREATE TABLE DSSMDOBJSECU
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    TRUST_ID               UNIQUEIDENTIFIER NOT NULL,
    RIGHTS                  INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL
);
 
ALTER TABLE DSSMDOBJSECU ADD CONSTRAINT PK_OBJSECU PRIMARY KEY (PROJECT_ID, OBJECT_ID, RIGHTS, TRUST_ID);
CREATE INDEX IX_OBJSECU_OBJECT ON DSSMDOBJSECU (OBJECT_ID);
 
-- Create DSSMDUSRACCT table
CREATE TABLE DSSMDUSRACCT
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    LOGIN                   NVARCHAR(250) NOT NULL,
    PASSWD                  NVARCHAR(250) NULL,
    NTSID                   VARCHAR(250) NULL,
    DBLOGIN                 NVARCHAR(50) NULL,
    ISGROUP                 SMALLINT NULL,
    LDAP_DN                        NVARCHAR(1000) DEFAULT '',
    U_ID                        NVARCHAR(1000) DEFAULT '',
LDAP_DN_HASHED        NVARCHAR(50) DEFAULT '',
    U_ID_HASHED        NVARCHAR(50) DEFAULT ''
);
 
ALTER TABLE DSSMDUSRACCT ADD CONSTRAINT PK_USRACCT PRIMARY KEY (OBJECT_ID, PROJECT_ID);
 
CREATE INDEX IX_USRACCT_LOGIN ON DSSMDUSRACCT (LOGIN);
CREATE INDEX IX_USRACCT_DBLOGIN ON DSSMDUSRACCT (DBLOGIN);
CREATE INDEX IX_USRACCT_NTSID ON DSSMDUSRACCT (NTSID);
CREATE INDEX IX_USRACCT_LDAP_DN_HASHED ON DSSMDUSRACCT (LDAP_DN_HASHED);
CREATE INDEX IX_USRACCT_UID_HASHED ON DSSMDUSRACCT (U_ID_HASHED);
 
-- Create DSSMDLNKPROP table
CREATE TABLE DSSMDLNKPROP
(
    LINKITEM_ID            UNIQUEIDENTIFIER NOT NULL,
    PROPSET_ID             UNIQUEIDENTIFIER NOT NULL,
    PROP_ID                 INT NOT NULL,
    VAL_SEQ                 INT NOT NULL,
    PROP_VAL                NVARCHAR(2000) NULL,
    PROP_TYPE               SMALLINT NOT NULL
);
 
ALTER TABLE DSSMDLNKPROP ADD CONSTRAINT PK_LNKPROP PRIMARY KEY (LINKITEM_ID, PROPSET_ID, PROP_ID, VAL_SEQ);
 
-- Create DSSMDLNKITEM table
CREATE TABLE DSSMDLNKITEM
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    LINKITEM_ID            UNIQUEIDENTIFIER NOT NULL,
    LINK_ID                UNIQUEIDENTIFIER NOT NULL,
    OBJECT_TYPE             INT NOT NULL
);
 
ALTER TABLE DSSMDLNKITEM ADD CONSTRAINT PK_LNKITEM PRIMARY KEY (PROJECT_ID, LINK_ID, LINKITEM_ID, OBJECT_ID);
 
CREATE INDEX IX_LNKITEM_ITEM ON DSSMDLNKITEM (LINKITEM_ID);
CREATE INDEX IX_LNKITEM_OBJPROJ ON DSSMDLNKITEM (OBJECT_ID, PROJECT_ID);
 
-- Populate DSSMDSYSPROP table
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('VERSION', '9010');
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('ENCODING', 'UCS2');
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('ENCRYPTIONLEVEL', '0');
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXVARCOLUMNLEN', '2000');
 
-- @@1
 
DROP TABLE DSSMDOBJTRNS;
DROP TABLE DSSMDJRNINFO;
DROP TABLE DSSMDJRNOBJS;
DROP TABLE DSSMDJRNOBJD;
DROP TABLE DSSMDJRNOBJC;
DROP TABLE DSSMDJRNLNKS;
 
-- Update DSSMDOBJINFO table
ALTER TABLE DSSMDOBJINFO ADD LOCALE INT DEFAULT 0 NOT NULL;
ALTER TABLE DSSMDOBJINFO ADD EXTENDED_TYPE INT DEFAULT 0 NOT NULL;
ALTER TABLE DSSMDOBJINFO ADD VIEW_MEDIA INT DEFAULT 0 NOT NULL;
ALTER TABLE DSSMDOBJINFO ADD ICON_PATH NVARCHAR(250) DEFAULT '';
 
-- Update DSSMDUSRACCT table
ALTER TABLE DSSMDUSRACCT ALTER COLUMN LOGIN NVARCHAR(250) NOT NULL;
 
-- Update DSSMDOBJDEFN table
ALTER TABLE DSSMDOBJDEFN ALTER COLUMN DEFINITION_SEQ INT NOT NULL;
 
-- Update DSSMDOBJPROP table
ALTER TABLE DSSMDOBJPROP ALTER COLUMN VALUE_SEQ INT NOT NULL;
 
-- Update DSSMDOBJCMNT table
ALTER TABLE DSSMDOBJCMNT ADD LOCALE INT DEFAULT 0 NOT NULL;
ALTER TABLE DSSMDOBJCMNT ALTER COLUMN COMMENT_SEQ INT NOT NULL;
 
-- Update DSSMDLNKPROP table
ALTER TABLE DSSMDLNKPROP ALTER COLUMN VAL_SEQ INT NOT NULL;
 
-- Create DSSMDOBJTRNS table
CREATE TABLE DSSMDOBJTRNS
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    PROPERTY                INT NOT NULL,
    LOCALE                  INT NOT NULL,
    TRANSLATION             NVARCHAR(250) NULL
);
 
ALTER TABLE DSSMDOBJTRNS ADD CONSTRAINT PK_OBJTRNS PRIMARY KEY (PROJECT_ID, OBJECT_ID,  PROPERTY, LOCALE);
CREATE INDEX IX_OBJTRNS_OBJECT ON DSSMDOBJTRNS (OBJECT_ID);
CREATE INDEX IX_OBJTRNS_TRNS ON DSSMDOBJTRNS (TRANSLATION);
CREATE INDEX IX_OBJTRNS_LCLPROP ON DSSMDOBJTRNS (LOCALE, PROPERTY);
 
-- Create DSSMDJRNINFO table
CREATE TABLE DSSMDJRNINFO
(
    TRANSACTION_ID         UNIQUEIDENTIFIER NOT NULL,
    TRANSACTION_PROJECT_ID UNIQUEIDENTIFIER NOT NULL,
    TRANSACTION_NAME        NVARCHAR(250) NULL,
    TRANSACTION_TIMESTAMP   DATETIME NOT NULL,
    TRANSACTION_TYPE        INT NOT NULL,
    TRANSACTION_SOURCE      INT NOT NULL,
    SESSION_ID             UNIQUEIDENTIFIER NOT NULL,
    USER_ID                UNIQUEIDENTIFIER NOT NULL,
    MACHINE                 NVARCHAR(250) NOT NULL
);
 
ALTER TABLE DSSMDJRNINFO ADD CONSTRAINT PK_JRNINFO PRIMARY KEY (TRANSACTION_ID, TRANSACTION_PROJECT_ID);
 
CREATE INDEX IX_JRNINFO_TS ON DSSMDJRNINFO (TRANSACTION_TIMESTAMP);
 
-- Create DSSMDJRNOBJS table
CREATE TABLE DSSMDJRNOBJS
(
    TRANSACTION_ID         UNIQUEIDENTIFIER NOT NULL,
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    CHANGE_TYPE             INT NOT NULL
);
 
ALTER TABLE DSSMDJRNOBJS ADD CONSTRAINT PK_JRNOBJS PRIMARY KEY (TRANSACTION_ID, OBJECT_ID, PROJECT_ID);
 
-- Create DSSMDJRNOBJD table
CREATE TABLE DSSMDJRNOBJD
(
    TRANSACTION_ID         UNIQUEIDENTIFIER NOT NULL,
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    OBJECT_NAME                        NVARCHAR(250) NULL,
    OBJECT_TYPE             INT NOT NULL,
    SUB_TYPE                INT NOT NULL
);
 
ALTER TABLE DSSMDJRNOBJD ADD CONSTRAINT PK_JRNOBJD PRIMARY KEY (TRANSACTION_ID, OBJECT_ID, PROJECT_ID);
 
-- Create DSSMDJRNOBJC table
CREATE TABLE DSSMDJRNOBJC
(
    TRANSACTION_ID         UNIQUEIDENTIFIER NOT NULL,
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    COMMENT_ID              INT NOT NULL,
    COMMENT_SEQUENCE_ID     INT NOT NULL,
    COMMENT_VAL             NVARCHAR(2000) NULL
);
 
ALTER TABLE DSSMDJRNOBJC ADD CONSTRAINT PK_JRNOBJC PRIMARY KEY (TRANSACTION_ID, OBJECT_ID, PROJECT_ID, COMMENT_ID, COMMENT_SEQUENCE_ID);
 
-- Create DSSMDJRNLNKS table
CREATE TABLE DSSMDJRNLNKS
(
    TRANSACTION_ID         UNIQUEIDENTIFIER NOT NULL,
    LINK_ID                UNIQUEIDENTIFIER NOT NULL,
    LINK_PROJECT_ID        UNIQUEIDENTIFIER NOT NULL,
    LINKITEM_ID            UNIQUEIDENTIFIER NOT NULL,
    CHANGE_TYPE             INT NOT NULL,
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    OBJECT_TYPE             INT NOT NULL
);
 
ALTER TABLE DSSMDJRNLNKS ADD CONSTRAINT PK_JRNLNKS PRIMARY KEY (TRANSACTION_ID, LINKITEM_ID, OBJECT_ID, PROJECT_ID);
 
-- NCS Table Creation
-- Drop existing NCS repository
DROP TABLE DSSCSSUBINST;
DROP TABLE DSSCSPSNLZTN;
DROP TABLE DSSCSRINSTRG;
DROP TABLE DSSCSCONTACT;
DROP TABLE DSSCSADDRESS;
DROP TABLE DSSCSRCOLCON;
DROP TABLE DSSCSSYSPROP;
 
-- Create DSSCSSUBINST table
CREATE TABLE DSSCSSUBINST
(
INST_ID                        CHAR(32)         NOT NULL ,
DISP_NAME                 NVARCHAR(250)         NULL ,
STATUS                        INTEGER         NOT NULL ,
CREATION_TIME                DATETIME         NOT NULL ,
PARENT_ID                CHAR(32)         NOT NULL ,
PROJECT_ID                CHAR(32)         NOT NULL ,
DATA_ID                        CHAR(32)        NOT NULL ,
DATA_TYPE                INTEGER                NOT NULL ,
IS_PERSONALIZED                SMALLINT        NOT NULL ,
CHANGEABILITY                SMALLINT        NOT NULL ,
OWNER_ID                CHAR(32)        NOT NULL ,
RECIPIENT_ID                CHAR(32)        NOT NULL ,
TRIGGER_ID                CHAR(32)        NOT NULL ,
DELIVERY_TYPE                INTEGER                NOT NULL ,
ADDRESS_ID                CHAR(32)        NOT NULL ,
NOTIFICATION_ADDR_ID        CHAR(32)         NOT NULL ,
VERSION_ID                CHAR(32)        NOT NULL ,
MODIFICATION_TIME        DATETIME        NOT NULL ,
INITIAL_ID                CHAR(32)         NOT NULL
);
 
ALTER TABLE DSSCSSUBINST ADD CONSTRAINT PK_SUBINST PRIMARY KEY (INST_ID);
 
-- Create DSSCSPSNLZTN table
CREATE TABLE DSSCSPSNLZTN
(
INST_ID                        CHAR(32)         NOT NULL ,
SUB_ID                        INTEGER                NOT NULL ,
VAL_SEQ                        INTEGER                NOT NULL ,
P_VALUE                        NVARCHAR(1024)        NOT NULL
);
 
ALTER TABLE DSSCSPSNLZTN ADD CONSTRAINT PK_PSNLZTN PRIMARY KEY (INST_ID, SUB_ID, VAL_SEQ);
 
-- Create DSSCSRINSTRG table
CREATE TABLE DSSCSRINSTRG
(
INST_ID                        CHAR(32)         NOT NULL ,
TRIGGER_ID                CHAR(32)         NOT NULL
);
 
ALTER TABLE DSSCSRINSTRG ADD CONSTRAINT PK_RINSTRG PRIMARY KEY (INST_ID, TRIGGER_ID);
 
-- Create DSSCSCONTACT table
CREATE TABLE DSSCSCONTACT
(
CONTACT_ID                CHAR(32)         NOT NULL ,
LOGIN                        NVARCHAR(250)         NOT NULL ,
PASSWD                        NVARCHAR(250)        NULL ,
CONTACT_TYPE                INTEGER                NOT NULL ,
STATUS                        INTEGER                NOT NULL ,
DESCRIPTION                NVARCHAR(250)   NULL ,
MSTRUSER_ID                CHAR(32)         NOT NULL ,
VERSION_ID                CHAR(32)        NOT NULL ,
CREATION_TIME                DATETIME        NOT NULL ,
MODIFICATION_TIME        DATETIME        NOT NULL
);
 
ALTER TABLE DSSCSCONTACT ADD CONSTRAINT PK_CONTACT PRIMARY KEY (CONTACT_ID);
 
-- Create DSSCSADDRESS table
CREATE TABLE DSSCSADDRESS
(
CONTACT_ID                CHAR(32)         NOT NULL ,
ADDRESS_ID                CHAR(32)        NOT NULL ,
DISP_NAME                NVARCHAR(250)        NOT NULL ,
ADDRESS                        NVARCHAR(250)        NOT NULL ,
DELIVERY_TYPE                INTEGER                NOT NULL ,
IS_DEFAULT                SMALLINT        NOT NULL ,
DEVICE_ID                CHAR(32)        NOT NULL ,
VERSION_ID                CHAR(32)        NOT NULL ,
CREATION_TIME                DATETIME        NOT NULL ,
MODIFICATION_TIME        DATETIME        NOT NULL
);
 
ALTER TABLE DSSCSADDRESS ADD CONSTRAINT PK_ADDRESS PRIMARY KEY (ADDRESS_ID);
 
-- Create DSSCSRCOLCON table
CREATE TABLE DSSCSRCOLCON
(
CONTACT_ID                CHAR(32)         NOT NULL ,
COLLECTION_ID                CHAR(32)        NOT NULL
);
 
ALTER TABLE DSSCSRCOLCON ADD CONSTRAINT PK_RCOLCON PRIMARY KEY (CONTACT_ID, COLLECTION_ID);
 
-- Create DSSCSSYSPROP table
CREATE TABLE DSSCSSYSPROP
(
PROP_NAME        VARCHAR(250) NOT NULL,
PROP_VAL        VARCHAR(250) NOT NULL
);
 
ALTER TABLE DSSCSSYSPROP ADD CONSTRAINT PK_CSSYSPROP PRIMARY KEY (PROP_NAME);
 
-- Populate DSSCSSYSPROP table
INSERT INTO DSSCSSYSPROP (PROP_NAME, PROP_VAL) VALUES ('VERSION', '8.1');
INSERT INTO DSSCSSYSPROP (PROP_NAME, PROP_VAL) VALUES ('ENCODING', 'UCS2');
INSERT INTO DSSCSSYSPROP (PROP_NAME, PROP_VAL) VALUES ('CHARCHUNKSIZE', '1024');
INSERT INTO DSSCSSYSPROP (PROP_NAME, PROP_VAL) VALUES ('BINCHUNKSIZE', '32000');
INSERT INTO DSSCSSYSPROP (PROP_NAME, PROP_VAL) VALUES ('INLISTMAX', '100');
 
-- @@2
 
DROP TABLE DSSMDOBJDEF2;
DROP TABLE DSSMDOBJLOCK;
 
-- Update DSSMDUSRACCT table
ALTER TABLE DSSMDUSRACCT ADD LDAP_DN NVARCHAR(1000) DEFAULT '';
ALTER TABLE DSSMDUSRACCT ADD U_ID NVARCHAR(1000) DEFAULT '';
 
-- Create DSSMDOBJDEF2 table
CREATE TABLE DSSMDOBJDEF2
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    DEFINITION_SEQ          INT NOT NULL,
    DEFINITION              VARCHAR(4000) NOT NULL
);
 
ALTER TABLE DSSMDOBJDEF2 ADD CONSTRAINT PK_OBJDEF2 PRIMARY KEY (PROJECT_ID, OBJECT_ID, DEFINITION_SEQ);
CREATE INDEX IX_OBJDEF2_OBJECT ON DSSMDOBJDEF2 (OBJECT_ID);
 
-- Create DSSMDOBJLOCK table
CREATE TABLE DSSMDOBJLOCK
(
    PROJECT_ID   UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID    UNIQUEIDENTIFIER NOT NULL,
    OBJECT_TYPE   INT NOT NULL,
    LOCK_SCOPE    SMALLINT NOT NULL,
    LOCK_TYPE     SMALLINT NOT NULL,
    LOCK_SOURCE   INT NOT NULL,
    LOCK_TIME     DATETIME NOT NULL,
    SESSION_ID   UNIQUEIDENTIFIER NOT NULL,
    USER_ID      UNIQUEIDENTIFIER NOT NULL,
    MACHINE       NVARCHAR(250) NULL,
    COMMENT_VAL   NVARCHAR(250) NULL
);
 
ALTER TABLE DSSMDOBJLOCK ADD CONSTRAINT PK_OBJLOCK PRIMARY KEY (OBJECT_ID, PROJECT_ID); 
 
-- Update DSSMDSYSPROP table
DELETE FROM DSSMDSYSPROP WHERE NAME = 'GENERATEINSERTCOLUMNLIST';
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('GENERATEINSERTCOLUMNLIST', '2'); 
DELETE FROM DSSMDSYSPROP WHERE NAME = 'RDBMS';
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('RDBMS', 'SQLSERVER2008');
 
-- @@SECU
 
-- Update DSSMDOBJSECU table
DROP TABLE TMPDSSMDOBJSECU;
CREATE TABLE TMPDSSMDOBJSECU
(
    PROJECT_ID             CHAR(32) NOT NULL,
    OBJECT_ID              CHAR(32) NOT NULL,
    TRUST_ID               CHAR(32) NOT NULL,
    RIGHTS                  INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL
);
INSERT INTO TMPDSSMDOBJSECU SELECT DISTINCT PROJECT_ID, OBJECT_ID, TRUST_ID, RIGHTS, OBJECT_TYPE FROM DSSMDOBJSECU;
DROP TABLE DSSMDOBJSECU;
CREATE TABLE DSSMDOBJSECU
(
    PROJECT_ID             CHAR(32) NOT NULL,
    OBJECT_ID              CHAR(32) NOT NULL,
    TRUST_ID               CHAR(32) NOT NULL,
    RIGHTS                  INT NOT NULL,
    OBJECT_TYPE             INT NOT NULL
);
INSERT INTO DSSMDOBJSECU SELECT PROJECT_ID, OBJECT_ID, TRUST_ID, RIGHTS, OBJECT_TYPE FROM TMPDSSMDOBJSECU;
DROP TABLE TMPDSSMDOBJSECU;
 
-- @@4
 
-- @@5
 
DROP TABLE DSSMDOBJBLOB;
 
-- Create DSSMDOBJBLOB table
CREATE TABLE DSSMDOBJBLOB
(
    PROJECT_ID             UNIQUEIDENTIFIER NOT NULL,
    OBJECT_ID              UNIQUEIDENTIFIER NOT NULL,
    DEFINITION_SEQ          INT NOT NULL,
    DEFINITION              VARCHAR(8000) NOT NULL
);
 
ALTER TABLE DSSMDOBJBLOB ADD CONSTRAINT PK_OBJBLOB PRIMARY KEY (PROJECT_ID, OBJECT_ID, DEFINITION_SEQ);
CREATE INDEX IX_OBJBLOB_OBJECT ON DSSMDOBJBLOB (OBJECT_ID);
 
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXBLOBCOLUMNLEN', '8000');
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('MAXBLOBSIZETHRESHOLD', '64000');
 
-- @@6
 
--1. create functions and procedures
--BEWARE:function dbo.fn_UniqueidentifierToCharMSTR must to be created no matter creating a new md shell or update the md schema.
 
DROP FUNCTION dbo.fn_UniqueidentifierToCharMSTR;
CREATE FUNCTION dbo.fn_UniqueidentifierToCharMSTR(@charid CHAR(36)) 
RETURNS CHAR(32)
AS 
BEGIN 
DECLARE @tpid VARCHAR(32) 
SET @tpid = SUBSTRING(@charid, 1, 8) 
SET @tpid = @tpid + SUBSTRING(@charid, 15, 4) 
SET @tpid = @tpid + SUBSTRING(@charid,  10, 4) 
SET @tpid = @tpid + SUBSTRING(@charid, 27, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 25, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 22, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 20, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 35, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 33, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 31, 2) 
SET @tpid = @tpid + SUBSTRING(@charid, 29, 2) 
RETURN @tpid 
END;
 
DROP FUNCTION dbo.fn_CharToUniqueidentifier;
CREATE FUNCTION dbo.fn_CharToUniqueidentifier(@charid CHAR(32))
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @tpid VARCHAR(36)
DECLARE @uid         UNIQUEIDENTIFIER
SET @tpid = SUBSTRING(@charid, 1, 8) + '-'
SET @tpid = @tpid + SUBSTRING(@charid, 13, 4) + '-'
SET @tpid = @tpid + SUBSTRING(@charid,   9, 4) + '-'
SET @tpid = @tpid + SUBSTRING(@charid, 23, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 21, 2) + '-'
SET @tpid = @tpid + SUBSTRING(@charid, 19, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 17, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 31, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 29, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 27, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 25, 2)
SET @uid = CAST(@tpid AS UNIQUEIDENTIFIER)
RETURN @uid
END;
 
DROP PROCEDURE sp_ModifyTableColumnType_CharToUniqueidentifier;
CREATE PROCEDURE sp_ModifyTableColumnType_CharToUniqueidentifier
(
@table                 VARCHAR(128)
)
AS
BEGIN
DECLARE @col_cursor CURSOR
SET @col_cursor = CURSOR DYNAMIC SCROLL FOR 
SELECT [name], xtype, length FROM syscolumns
WHERE id = OBJECT_ID(@table) 
ORDER BY colid;
 
DECLARE @column                VARCHAR(128)
DECLARE @type_id         INT
DECLARE @length                INT
 
OPEN @col_cursor;
FETCH NEXT FROM @col_cursor INTO @column, @type_id, @length
 
DECLARE @sql_alter_init VARCHAR(64)
DECLARE @sql_alter                 VARCHAR(256)
 
SET @sql_alter_init = 'ALTER TABLE ' + @table + ' alter column '
 
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type_id = 175 AND @length = 32
BEGIN
SET @sql_alter = @sql_alter_init + @column + ' CHAR(36) NOT NULL '
EXECUTE (@sql_alter)
END
 
FETCH NEXT FROM @col_cursor INTO @column, @type_id, @length
END
 
DECLARE @flag                         INT
DECLARE @sql_update                VARCHAR(1024)
SET @sql_update = 'UPDATE ' + @table + ' SET '
FETCH FIRST FROM @col_cursor INTO @column, @type_id, @length
 
SET @flag = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type_id = 175 AND @length = 36
BEGIN
SET @flag = 1
SET @sql_update = @sql_update + @column + ' = dbo.fn_CharToUniqueidentifier(' + @column + '), '
END
FETCH NEXT FROM @col_cursor INTO @column, @type_id, @length
END        
IF @flag = 0
RETURN
 
SET @sql_update = SUBSTRING(@sql_update, 1, LEN(@sql_update) - 1)
EXECUTE (@sql_update)
 
FETCH FIRST FROM @col_cursor INTO @column, @type_id, @length
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type_id = 175 AND @length = 36
BEGIN
SET @sql_alter = @sql_alter_init + @column + ' UNIQUEIDENTIFIER NOT NULL'
EXECUTE (@sql_alter)
END
 
FETCH NEXT FROM @col_cursor INTO @column, @type_id, @length
END        
END;
 
-- If we are updating a pre-Orion MD, the following 9 SPs need to be run
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJINFO';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJDEFN';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJDEPN';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJPROP';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJCMNT';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJSECU';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDUSRACCT';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDLNKPROP';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDLNKITEM';
 
-- If we are updating an Orion MD, the above 9 SPs plus these 6 SPs need to be run
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJTRNS';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDJRNINFO';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDJRNOBJS';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDJRNOBJD';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDJRNOBJC';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDJRNLNKS';
 
-- If we are updating a 901 or 902 MD, the above 15 SPs plus these 2 SPs need to be run
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJDEF2';
exec  sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJLOCK';
 
-- If we are updating a 903 MD, the above 17 SPs plus this one need to be run
exec   sp_ModifyTableColumnType_CharToUniqueidentifier 'DSSMDOBJBLOB';
 
-- @@7
 
-- Create trigger on DSSMDOBJINFO table
DROP TRIGGER TD_DSSMDOBJINFO;
CREATE TRIGGER TD_DSSMDOBJINFO ON DSSMDOBJINFO FOR DELETE AS
BEGIN
    DELETE DSSMDOBJDEFN
        FROM DSSMDOBJDEFN,DELETED
            WHERE
                DSSMDOBJDEFN.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJDEFN.OBJECT_ID = DELETED.OBJECT_ID
DELETE DSSMDOBJDEF2
        FROM DSSMDOBJDEF2,DELETED
            WHERE
                DSSMDOBJDEF2.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJDEF2.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJBLOB
        FROM DSSMDOBJBLOB,DELETED
            WHERE
                DSSMDOBJBLOB.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJBLOB.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJDEPN
        FROM DSSMDOBJDEPN,DELETED
            WHERE
                DSSMDOBJDEPN.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJDEPN.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJPROP
        FROM DSSMDOBJPROP,DELETED
            WHERE
                DSSMDOBJPROP.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJPROP.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJCMNT
        FROM DSSMDOBJCMNT,DELETED
            WHERE
                DSSMDOBJCMNT.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJCMNT.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJSECU
        FROM DSSMDOBJSECU,DELETED
            WHERE
                DSSMDOBJSECU.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJSECU.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDUSRACCT
        FROM DSSMDUSRACCT,DELETED
            WHERE
                DSSMDUSRACCT.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDUSRACCT.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJTRNS
        FROM DSSMDOBJTRNS,DELETED
            WHERE
                DSSMDOBJTRNS.PROJECT_ID = DELETED.PROJECT_ID AND
                DSSMDOBJTRNS.OBJECT_ID = DELETED.OBJECT_ID
    DELETE DSSMDOBJLOCK
            FROM DSSMDOBJLOCK, DELETED
                 WHERE
                    DSSMDOBJLOCK.PROJECT_ID = DELETED.PROJECT_ID AND
              DSSMDOBJLOCK.OBJECT_ID = DELETED.OBJECT_ID
    RETURN
ERROR:
    ROLLBACK TRANSACTION
END;
 
-- @@8
 
DROP TABLE DSSCSBADGETB;
DROP TABLE DSSCSDEVCKEY;
 
-- Create DSSCSBADGETB table
CREATE TABLE DSSCSBADGETB
(
    TOKEN                NVARCHAR(250)        NOT NULL ,
    BADGE                INTEGER                NOT NULL ,
    ADDRESS_DEVICE_ID        CHAR(32)        NOT NULL
);
 
ALTER TABLE DSSCSBADGETB ADD CONSTRAINT PK_BADGETB PRIMARY KEY (TOKEN, ADDRESS_DEVICE_ID);
 
-- Create DSSCSDEVCKEY table
CREATE TABLE DSSCSDEVCKEY
(
    DEVICE_TOKEN                NVARCHAR(250)        NOT NULL ,
    DEVICE_KEY                        NVARCHAR(250)        NOT NULL ,
    ADDRESS_DEVICE_ID                CHAR(32)        NOT NULL
);
 
ALTER TABLE DSSCSDEVCKEY ADD CONSTRAINT PK_DEVCKEY PRIMARY KEY (DEVICE_TOKEN, ADDRESS_DEVICE_ID);
 
 
-- @@9
 
-- Update DSSMDUSRACCT table
ALTER TABLE DSSMDUSRACCT ADD LDAP_DN_HASHED NVARCHAR(50) DEFAULT '';
ALTER TABLE DSSMDUSRACCT ADD U_ID_HASHED NVARCHAR(50) DEFAULT '';
 
UPDATE DSSMDUSRACCT SET LDAP_DN_HASHED = LEFT(LDAP_DN, 50);
UPDATE DSSMDUSRACCT SET U_ID_HASHED = RIGHT(U_ID, 50);
 
 
-- @@FINAL
 
-- All finalized actions are done here.
DELETE FROM DSSMDSYSPROP WHERE NAME='SCHEMAVERSION';
INSERT INTO DSSMDSYSPROP (NAME, PROP_VAL) VALUES ('SCHEMAVERSION', '9');

Leave a Reply

Your email address will not be published. Required fields are marked *