CTX-Configuration-Deployment-Script.sql

/*
Deployment script for Cortex-ConfigStore

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO

:setvar CortexDBUser "domain\CTX_App_User_Service_Account"
:setvar DatabaseFilePath "C:\Cortex Databases"
:setvar DatabaseLogPath "C:\Cortex Databases"
:setvar Distribution_DataPath "C:\Cortex Databases\Distribution"
:setvar Distribution_LogPath "C:\Cortex Databases\Distribution"
:setvar InstanceName "LIVE_DBServerName\LIVE_InstanceName"
:setvar isReplicated "true"
:setvar MachineName "LIVE_DBServerName"
:setvar REPL_Admin_User "domain\CTX_SQL_Admin_User"
:setvar REPL_Working_Directory "C:\Cortex Databases\Replication Data"
:setvar ResilientInstanceName "DR_DBServerName\DR_InstanceName"
:setvar DatabaseName "Cortex-ConfigStore"
:setvar DefaultFilePrefix "Cortex-ConfigStore"


GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [master];


GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating database $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [Cortex_ConfigStore_Data], FILENAME = '$(DatabaseFilePath)\$(DatabaseName)\Datafile\$(DatabaseName).mdf')
    LOG ON (NAME = [Cortex_ConfigStore_log], FILENAME = '$(DatabaseLogPath)\$(DatabaseName)\Logfile\$(DatabaseName).ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
										
							   
									
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS 
    WITH ROLLBACK IMMEDIATE;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = NONE 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
                MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
                DELAYED_DURABILITY = DISABLED 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
    END


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.	
 Use SQLCMD syntax to include a file in the pre-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
USE [$(DatabaseName)];
--Create logins if they don't exist
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'$(CortexDBUser)')
BEGIN TRY
	CREATE LOGIN [$(CortexDBUser)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
	PRINT 'Created login $(CortexDBUser)'
END TRY
BEGIN CATCH
	PRINT '$(CortexDBUser) login does not exist'
END CATCH 
	

																						
		   
																				
										   
		 
			 
												  
		   


IF ('$(isReplicated)' = 'true')
BEGIN
IF NOT EXISTS (SELECT srv.name 
		   FROM   sys.servers srv 
	   	   WHERE  srv.server_id != 0 
		   AND srv.name = N'REPL-$(ResilientInstanceName)')
BEGIN

	EXEC master.dbo.sp_addlinkedserver @server = N'REPL-$(ResilientInstanceName)', @srvproduct='', @provider='SQLOLEDB', @datasrc=N'$(ResilientInstanceName)'

	EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REPL-$(ResilientInstanceName)',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'collation compatible', @optvalue=N'false'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'data access', @optvalue=N'true'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'dist', @optvalue=N'false'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'pub', @optvalue=N'false'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'rpc', @optvalue=N'true'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'rpc out', @optvalue=N'true'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'sub', @optvalue=N'false'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'connect timeout', @optvalue=N'0'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'collation name', @optvalue=null

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'lazy schema validation', @optvalue=N'false'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'query timeout', @optvalue=N'0'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'use remote collation', @optvalue=N'true'

	EXEC master.dbo.sp_serveroption @server=N'REPL-$(ResilientInstanceName)', @optname=N'remote proc transaction promotion', @optvalue=N'true'

END


END
GO

GO
PRINT N'Creating Table [dbo].[ADGroup]...';


GO
CREATE TABLE [dbo].[ADGroup] (
    [ID]          INT           NOT NULL,
    [GroupName]   VARCHAR (50)  NULL,
    [Description] VARCHAR (500) NULL,
    CONSTRAINT [PK_ADGroup] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [unq_ADGroupConstraint] UNIQUE NONCLUSTERED ([GroupName] ASC)
);


GO
PRINT N'Creating Table [dbo].[AreaADGroupsLink]...';


GO
CREATE TABLE [dbo].[AreaADGroupsLink] (
    [ID]        INT NOT NULL,
    [AreaID]    INT NOT NULL,
    [ADGroupID] INT NOT NULL,
    CONSTRAINT [PK_AreaADGroupsLink] PRIMARY KEY CLUSTERED ([ID] ASC)
);


GO
PRINT N'Creating Table [dbo].[Areas]...';


GO
CREATE TABLE [dbo].[Areas] (
    [ID]          INT          NOT NULL,
    [Area]        VARCHAR (50) NOT NULL,
    [Description] VARCHAR (50) NULL,
    CONSTRAINT [PK_Areas-v2] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT [Area_Unique] UNIQUE NONCLUSTERED ([Area] ASC) ON [PRIMARY]
) ON [PRIMARY];


GO
PRINT N'Creating Table [dbo].[Customers]...';


GO
CREATE TABLE [dbo].[Customers] (
    [ID]          INT          NOT NULL,
    [Customer]    VARCHAR (50) NOT NULL,
    [Description] VARCHAR (50) NULL,
    CONSTRAINT [PK_Customers-v2] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT [Cust_Unique] UNIQUE NONCLUSTERED ([Customer] ASC) ON [PRIMARY]
) ON [PRIMARY];


GO
PRINT N'Creating Table [dbo].[Environments]...';


GO
CREATE TABLE [dbo].[Environments] (
    [ID]          INT          NOT NULL,
    [Environment] VARCHAR (50) NOT NULL,
    [Description] VARCHAR (50) NULL,
    CONSTRAINT [PK_Environments-v2] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT [Env_Unique] UNIQUE NONCLUSTERED ([Environment] ASC) ON [PRIMARY]
) ON [PRIMARY];


GO
PRINT N'Creating Table [dbo].[Param_Name]...';


GO
CREATE TABLE [dbo].[Param_Name] (
    [ID]          INT           NOT NULL,
    [Param_Name]  VARCHAR (50)  NOT NULL,
    [Description] VARCHAR (500) NULL,
    CONSTRAINT [PK_Param_Name-v2] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT [Prm_Unique] UNIQUE NONCLUSTERED ([Param_Name] ASC) ON [PRIMARY]
) ON [PRIMARY];


GO
PRINT N'Creating Table [dbo].[Param_Values]...';


GO
CREATE TABLE [dbo].[Param_Values] (
    [ID]            INT           NOT NULL,
    [AreaID]        INT           NOT NULL,
    [CustomerID]    INT           NULL,
    [EnvironmentID] INT           NULL,
    [Param_NameID]  INT           NOT NULL,
    [Param_Value]   VARCHAR (MAX) NOT NULL,
    [Description]   VARCHAR (500) NULL,
    CONSTRAINT [PK_Param_Values-v2] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT [unique_prm] UNIQUE NONCLUSTERED ([AreaID] ASC, [CustomerID] ASC, [EnvironmentID] ASC, [Param_NameID] ASC) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];


GO
PRINT N'Creating Table [dbo].[SYS_Sequence]...';


GO
CREATE TABLE [dbo].[SYS_Sequence] (
    [TableName]      VARCHAR (100) NOT NULL,
    [ColumnName]     VARCHAR (100) NOT NULL,
    [SequenceNumber] BIGINT        NOT NULL,
    CONSTRAINT [PK_SYS_Sequence] PRIMARY KEY CLUSTERED ([TableName] ASC)
);


GO
PRINT N'Creating Table [dbo].[SYS_SETTINGS]...';


GO
CREATE TABLE [dbo].[SYS_SETTINGS] (
    [KEY]         VARCHAR (32)  NOT NULL,
    [VALUE]       VARCHAR (MAX) NOT NULL,
    [DESCRIPTION] VARCHAR (MAX) NULL,
    CONSTRAINT [PK_SYS_SETTINGS] PRIMARY KEY CLUSTERED ([KEY] ASC)
);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Param_Values_Areas]...';


GO
ALTER TABLE [dbo].[Param_Values]
    ADD CONSTRAINT [FK_Param_Values_Areas] FOREIGN KEY ([AreaID]) REFERENCES [dbo].[Areas] ([ID]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Param_Values_Customers]...';


GO
ALTER TABLE [dbo].[Param_Values]
    ADD CONSTRAINT [FK_Param_Values_Customers] FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customers] ([ID]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Param_Values_Environments]...';


GO
ALTER TABLE [dbo].[Param_Values]
    ADD CONSTRAINT [FK_Param_Values_Environments] FOREIGN KEY ([EnvironmentID]) REFERENCES [dbo].[Environments] ([ID]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Param_Values_Param_Name]...';


GO
ALTER TABLE [dbo].[Param_Values]
    ADD CONSTRAINT [FK_Param_Values_Param_Name] FOREIGN KEY ([Param_NameID]) REFERENCES [dbo].[Param_Name] ([ID]);


GO
PRINT N'Creating View [dbo].[CFG_View]...';


GO
CREATE VIEW [dbo].[CFG_View]
AS
	SELECT TOP 100 PERCENT A.Area,
					       C.Customer,
					       E.Environment,
					       PN.Param_name,
						   PV.Description,
					       PV.Param_value
	FROM   dbo.param_values AS PV
	INNER JOIN dbo.param_name AS PN
			ON PV.param_nameid = PN.id
	INNER JOIN dbo.areas AS A
			ON PV.areaid = A.id
	LEFT OUTER JOIN dbo.environments AS E
				ON PV.environmentid = E.id
	LEFT OUTER JOIN dbo.customers AS C
				ON PV.customerid = C.id
	ORDER BY A.Area,
			 C.Customer,
		     E.Environment,
		     PN.Param_name
GO
PRINT N'Creating View [dbo].[CGF_All_Permutations]...';


GO
CREATE VIEW [dbo].[CGF_All_Permutations] AS
SELECT T.Area
	 , T.Customer
	 , T.Environment
	 , T.Param_Name
	 , PV.ID
	 , PV.Param_Value
	 , PV.Description
FROM
(SELECT A.ID AS AreaID
	  , A.Area
	  , C.ID AS CustomerID
	  , C.Customer
	  , E.ID AS EnvironmentID
	  , E.Environment
	  , PN.ID AS ParamID
	  , PN.Param_Name
FROM Param_Name PN
CROSS JOIN Areas A
CROSS JOIN Customers C
CROSS JOIN Environments E
UNION
SELECT A.ID AS AreaID
	 , A.Area
	 , C.ID AS CustomerID
	 , C.Customer
	 , NULL AS EnvironmentID
	 , NULL Environment
	 , PN.ID AS ParamID
	 , PN.Param_Name
FROM Param_Name PN
CROSS JOIN Areas A
CROSS JOIN Customers C
UNION
SELECT A.ID AS AreaID
	 , A.Area
	 , NULL AS CustomerID
	 , NULL AS Customer
	 , E.ID AS EnvironmentID
	 , E.Environment
	 , PN.ID AS ParamID
	 , PN.Param_Name
FROM Param_Name PN
CROSS JOIN Areas A
CROSS JOIN Environments E
UNION
SELECT A.ID AS AreaID
	 , A.Area
	 , NULL AS CustomerID
	 , NULL AS Customer
	 , NULL AS EnvironmentID
	 , NULL AS Environment
	 , PN.ID AS ParamID
	 , PN.Param_Name
FROM Param_Name PN
CROSS JOIN Areas A) T
LEFT OUTER JOIN Param_Values PV
	ON ISNULL(PV.Param_NameID,-1) = ISNULL(T.ParamID,-1)
	AND ISNULL(PV.AreaID,-1) = ISNULL(T.AreaID,-1)
	AND ISNULL(PV.CustomerID,-1) = ISNULL(T.CustomerID,-1)
	AND ISNULL(PV.EnvironmentID,-1) = ISNULL(T.EnvironmentID,-1)
GO
PRINT N'Creating Function [dbo].[udf_Get_Parameter_Value]...';


GO

CREATE FUNCTION [dbo].[udf_Get_Parameter_Value](@pi_Param_Name varchar(200))  
RETURNS varchar(200) AS
BEGIN 
  declare  @return_value   varchar(200)

  select @return_value = [VALUE]
  from   SYS_SETTINGS
  where  [KEY] = @pi_Param_Name

  return @return_value
END
GO
PRINT N'Creating Function [dbo].[udf_GetSequenceNumber]...';


GO
CREATE FUNCTION [dbo].[udf_GetSequenceNumber]
(
	@i_sTableName	VARCHAR(100)
  , @i_sColumnName	VARCHAR(100)
)
RETURNS BIGINT 
AS
BEGIN 
  DECLARE  @nSequenceNumber BIGINT

  SELECT @nSequenceNumber = [SequenceNumber]
  FROM   SYS_Sequence
  WHERE  [TableName] = @i_sTableName
  AND    [ColumnName] = @i_sColumnName

  RETURN @nSequenceNumber
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_ADGroup]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_ADGroup]
   ON  [dbo].[ADGroup]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @ADGroupID int = (SELECT dbo.udf_GetSequenceNumber('ADGroup','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO ADGroup (ID
							,GroupName)
			SELECT @ADGroupID
				  ,inserted.GroupName
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @ADGroupID + 1
			WHERE  TableName = 'ADGroup'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_AreaADGroupsLink]...';


GO
CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_AreaADGroupsLink]
   ON  [dbo].[AreaADGroupsLink]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @AreaADGroupsLinkID int = (SELECT dbo.udf_GetSequenceNumber('AreaADGroupsLink','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO AreaADGroupsLink (ID
							,AreaID
							,ADGroupID)
			SELECT @AreaADGroupsLinkID
				  ,inserted.AreaID,inserted.ADGroupID
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @AreaADGroupsLinkID + 1
			WHERE  TableName = 'AreaADGroupsLink'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_Areas]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_Areas]
   ON  [dbo].[Areas]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @AreaID int = (SELECT dbo.udf_GetSequenceNumber('Areas','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO Areas (ID
							,Area
							,Description)
			SELECT @AreaID
				  ,inserted.Area
				  ,inserted.Description
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @AreaID + 1
			WHERE  TableName = 'Areas'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_Customers]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_Customers]
   ON  [dbo].[Customers]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @CustID int = (SELECT dbo.udf_GetSequenceNumber('Customers','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO Customers (ID
							,Customer
							,Description)
			SELECT @CustID
				  ,inserted.Customer
				  ,inserted.Description
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @CustID + 1
			WHERE  TableName = 'Customers'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_Environments]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_Environments]
   ON  [dbo].[Environments]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @EnvID int = (SELECT dbo.udf_GetSequenceNumber('Environments','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO Environments (ID
							,Environment
							,Description)
			SELECT @EnvID
				  ,inserted.Environment
				  ,inserted.Description
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @EnvID + 1
			WHERE  TableName = 'Environments'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_Param_Name]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_Param_Name]
   ON  [dbo].[Param_Name]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @PrmID int = (SELECT dbo.udf_GetSequenceNumber('Param_Name','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO Param_Name (ID
							,Param_Name
							,Description)
			SELECT @PrmID
				  ,inserted.Param_Name
				  ,inserted.Description
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @PrmID + 1
			WHERE  TableName = 'Param_Name'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Trigger [dbo].[TRG_InsteadOfInsert_Param_Values]...';


GO

CREATE TRIGGER [dbo].[TRG_InsteadOfInsert_Param_Values]
   ON  [dbo].[Param_Values]
   INSTEAD OF INSERT NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE  @CfgValID int = (SELECT dbo.udf_GetSequenceNumber('Param_Values','ID'));

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO Param_Values (ID
							,AreaID
							,CustomerID
							,EnvironmentID
							,Param_NameID
							,Param_Value
							,Description)
							
			SELECT @CfgValID
				  ,inserted.AreaID
				  ,inserted.CustomerID
				  ,inserted.EnvironmentID
				  ,inserted.Param_NameID
				  ,inserted.Param_Value
				  ,inserted.Description
			FROM  inserted

			UPDATE SYS_Sequence
			SET    SequenceNumber = @CfgValID + 1
			WHERE  TableName = 'Param_Values'
			AND    ColumnName = 'ID'

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		
		IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
	END CATCH
END
GO
PRINT N'Creating Function [dbo].[udf_split_string]...';


GO
-- =============================================
-- Author:		Ravi
-- Create date: 28-10-2021
-- Description:	comma separated values to list
-- =============================================
CREATE FUNCTION [dbo].[udf_split_string]
(
    @in_string VARCHAR(MAX),
    @delimiter VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
        WHILE LEN(@in_string) > 0
        BEGIN
            INSERT INTO @list(tuple)
            SELECT left(@in_string, charindex(@delimiter, @in_string+',') -1) as tuple
    
            SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), '')
        end
    RETURN 
END
					  
									  
							  
								   
						   
				 
			   
					 
					
  
												  


  
										   
			 
			  
				 
				
		 
				  
				  
	
					  
		   
					   
			   
						  
				  
					 
				  
				  
				  
					  
						 
	 
					 
		  
					  
			  
						 
					
					
				 
				  
				  
					  
	 
					 
		  
					  
					
						 
				 
					
				 
				  
				  
						 
	 
					 
		  
					  
					
						 
					   
					
				 
				  
					 
							   
													 
											   
													   
															 
GO
PRINT N'Creating Procedure [dbo].[usp_Get_Job_Status]...';


GO
CREATE PROCEDURE [dbo].[usp_Get_Job_Status]
   @job_name  SYSNAME
AS
SET NOCOUNT ON

DECLARE @job_id          UNIQUEIDENTIFIER
DECLARE @is_idle         INT
declare @status          int

--Check name exists
IF NOT EXISTS(SELECT 1
              FROM   msdb.dbo.sysjobs (nolock)
              where  name=@job_name)
BEGIN
  RAISERROR('The job name specified does not exist',16,1)
  RETURN 1
END

--Temp table to store results of xp_sqlagent_enum_jobs
create table #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                          last_run_date         INT              NOT NULL,
                          last_run_time         INT              NOT NULL,
                          next_run_date         INT              NOT NULL,
                          next_run_time         INT              NOT NULL,
                          next_run_schedule_id  INT              NOT NULL,
                          requested_to_run      INT              NOT NULL,
                          request_source        INT              NOT NULL,
                          request_source_id     sysname          COLLATE database_default NULL,
                          running               INT              NOT NULL,
                          current_step          INT              NOT NULL,
                          current_retry_attempt INT              NOT NULL,
                          job_state             INT              NOT NULL)

--Get the Job ID
EXECUTE  msdb.dbo.sp_verify_job_identifiers  '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

--Get Job Details
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa', @job_id -- SQLCMD?

--Get Job Status
/*
        1 Executing.
        2 Waiting for thread.
        3 Between retries.
        4 Idle.
        5 Suspended.
        7 Performing completion actions.
*/
SELECT @status=job_state
FROM #xp_results

DROP TABLE #xp_results

return @status
GO
PRINT N'Creating Procedure [dbo].[usp_InsertUpdate_ParamValues]...';


GO
-- =============================================
-- Author:		Pedro Nogueira
-- Create date: 21/04/2019
-- Description:	Insert/Update parameters into ParamValues table
-- =============================================
CREATE PROCEDURE [dbo].[usp_InsertUpdate_ParamValues] 
	-- Add the parameters for the stored procedure here
		@i_sAreaName varchar(50),
		@i_sParamName varchar(50),
		@i_sCustomerName varchar(50) = NULL,
		@i_sEnvironmentName varchar(50) = NULL,
		@i_sDescription varchar(500) = NULL,
		@i_sParamValue varchar(max)
AS
BEGIN


	DECLARE @nAreaID INT = (SELECT ID
							FROM Areas
							WHERE Area = @i_sAreaName)
		  , @nCustomerID INT = (SELECT ID
								FROM Customers
								WHERE Customer = @i_sCustomerName)
		  , @nEnvironmentID INT = (SELECT ID
								   FROM Environments
								   WHERE Environment = @i_sEnvironmentName)
		  , @nParamID INT = (SELECT ID
							 FROM Param_Name
						 	 WHERE Param_Name = @i_sParamName)


	IF NOT EXISTS(SELECT 1 
				  FROM Param_Values 
				  WHERE AreaID = @nAreaID
				  AND Param_NameID = @nParamID
				  AND ISNULL(EnvironmentID,-1) = ISNULL(@nEnvironmentID,-1)
				  AND ISNULL(CustomerID,-1) = ISNULL(@nCustomerID,-1))
	BEGIN
		INSERT INTO [dbo].[Param_Values]
			   ([AreaID]
			   ,[CustomerID]
			   ,[EnvironmentID]
			   ,[Param_NameID]
			   ,[Param_Value]
			   ,[Description])
		 VALUES
			   (@nAreaID
			  , @nCustomerID
			  , @nEnvironmentID
			  , @nParamID
			  , @i_sParamValue
			  , @i_sDescription)
	END
	ELSE
	BEGIN
		UPDATE [dbo].[Param_Values]
		SET Param_Value = @i_sParamValue,
			Description = @i_sDescription
		WHERE AreaID = @nAreaID
		AND Param_NameID = @nParamID
		AND ISNULL(EnvironmentID,-1) = ISNULL(@nEnvironmentID,-1)
		AND ISNULL(CustomerID,-1) = ISNULL(@nCustomerID,-1)
	END

END
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Add_Article]...';


GO

-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  19th October 2011
-- Description:  This procedure adds an article to a Publication and has replaced
--               the original procedures usp_REPL_Add_Merge_Article and
--               usp_REPL_TXN_Add_Article.
-- =================================================================================
-- Calls:        None
-- Called By:    usp_REPL_Create_Publication_Live_Transactional
--               usp_REPL_Create_Publication_CFG_Transactional
--               usp_REPL_Create_Publication_CFG_Merge
--               usp_REPL_Recreate_Publication_Live_Transactional
-- =================================================================================
-- Update Author:Chris Jenkins
-- Update Date:  3rd September 2012
-- Description:  1) Added code for a new Partitioning publication.  This replicates
--                  the partitioning call on the remote site rather than performing 
--                  all changes on the live site and replicating the changes.  This
--                  involves a large number of deletes and takes a lot of resources
--                  unnecessarily in live environments.
--               2) Tidied and used new SQL 2008 functionality where applicable.
--               3) Updated the merge schema options to avoid incompatible property
--                  warnings on install.
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Add_Article] 
   @i_sPubName         sysname
 , @i_sPubType         varchar(15)
 , @i_sArticleName     sysname
 , @i_sArticleType     varchar(50)
AS
DECLARE @nSchemaOption   binary(8)
      , @sType           varchar(16)
      , @sPreCreationCmd varchar(16)
SET NOCOUNT ON

IF @i_sPubType = 'Transactional'
BEGIN
	IF @i_sArticleType = 'table' 
	BEGIN
		SET @nSchemaOption = 0x0000000048045DDD
		SET @sType = 'logbased'
		SET @sPreCreationCmd = 'delete'
	END
	ELSE 
	BEGIN
		SET @nSchemaOption = 0x0000000008000001
		SET @sPreCreationCmd = 'none'
		IF (@i_sArticleType = 'procedure')
		BEGIN
			SET @sType = 'proc exec'
		END
		ELSE
		BEGIN
			SET @sType = @i_sArticleType + ' schema only'
		END
	END

	-- Add the article
	EXEC sp_addarticle @publication = @i_sPubName
					 , @article = @i_sArticleName
					 , @source_owner = N'dbo'
					 , @source_object = @i_sArticleName
					 , @destination_table = @i_sArticleName
					 , @type = @sType
					 , @description = null
					 , @pre_creation_cmd = @sPreCreationCmd
					 , @schema_option = @nSchemaOption
					 , @status = 16
					 , @vertical_partition = N'false'
					 , @filter = null
					 , @sync_object = null
END
ELSE --(@i_sPubType = 'Merge')
BEGIN
	SET @sPreCreationCmd = 'DROP'

    IF @i_sArticleType = 'table' 
	BEGIN
		SET @nSchemaOption = 0x000000B20C034FD1
		SET @sType = 'table'
	END
	ELSE
	BEGIN
		SET @nSchemaOption = 0x0000000008000001
		SET @sType = @i_sArticleType + ' schema only'
	END

	-- Add the merge articles for the retreived function
	EXEC sp_addmergearticle @publication = @i_sPubName
						  , @article = @i_sArticleName
						  , @source_owner = N'dbo'
						  , @source_object = @i_sArticleName
						  , @type = @sType
						  , @description = null
						  , @pre_creation_cmd = @sPreCreationCmd
						  , @creation_script = null
						  , @schema_option = @nSchemaOption
						  , @destination_owner = N'dbo'
						  , @force_reinit_subscription = 1
						  , @column_tracking = N'false'
						  , @vertical_partition = N'false'
						  , @verify_resolver_signature = 1
						  , @allow_interactive_resolver = N'false'
						  , @fast_multicol_updateproc = N'true'
						  , @subset_filterclause = null
						  , @destination_object = @i_sArticleName
						  , @check_permissions = 0
						  , @subscriber_upload_options = 0                          
						  , @force_invalidate_snapshot = 1
						  , @delete_tracking = N'true'
						  , @compensate_for_errors = N'false'
						  , @stream_blob_columns = N'false'
						  , @partition_options = 0

END
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Create_LogReader_Job]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  27th August 2010
-- Description:  This procedure Creates the Log Reader Job for a Transactional 
--               Publication
-- =================================================================================
-- Calls:        None.
-- Called By:    usp_REPL_Setup_Replication.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Renamed the procedure 
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Create_LogReader_Job]
   @i_sJobName     sysname
 , @i_sPublisher   sysname
 , @i_sDistributor sysname
 
AS
  set nocount on
  
  -- get replication details
  declare @sReplUser   varchar(100)
        , @sSchedName  varchar(100)
        , @nJobID      binary(16)  
        , @nReturnCode int    
        , @nAgentId    int
        , @sCommand    nvarchar(max)

  
  set @sReplUser   = dbo.udf_Get_Parameter_Value('REPL_Admin_User')
  set @sSchedName  = 'Replication agent schedule - ' + @i_sJobName
  set @nAgentId    = ident_current('distribution..MSlogReader_agents') + 1
  set @nReturnCode = 0

  if @nAgentId is null 
    set @nAgentId = 1
  
  -- create the REPL-Merge category if it doesn't exist
  if not exists (SELECT 1
                 FROM   msdb.dbo.syscategories
                 WHERE  name = N'REPL-LogReader')
    exec msdb.dbo.sp_add_category @name = N'REPL-LogReader'

  -- Delete the job with the same name (if it exists)
  SELECT @nJobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE  name = @i_sJobName

  if @nJobID is not null begin
    -- Delete the [local] job 
    exec msdb.dbo.sp_delete_job @job_name = @i_sJobName
    select @nJobID = NULL
  end 

  -- Add the job
  exec @nReturnCode = msdb.dbo.sp_add_job @job_id = @nJobID OUTPUT
                                        , @job_name = @i_sJobName
		                                , @owner_login_name = @sReplUser
		                                , @description = N'No description available.'
		                                , @category_name = N'REPL-LogReader'
		                                , @enabled = 1
		                                , @notify_level_email = 0
		                                , @notify_level_page = 0
                       	                , @notify_level_netsend = 0
		                                , @notify_level_eventlog = 0
		                                , @delete_level = 0 


  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  set @sCommand = 'sp_MSadd_LogReader_history @perfmon_increment = 0, ' +
                  '@agent_id = ' + convert(varchar, @nAgentId) + ', ' +
                  '@runstatus = 1, @comments = ''Starting agent.'''

  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 1
                                            , @step_name = N'Distribution Agent startup message.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'TSQL'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 0
                                            , @retry_interval = 0
                                            , @on_success_step_id = 0
                                            , @on_success_action = 3
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 3
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
  set @sCommand = '-Publisher [' + @i_sPublisher + '] ' +
                  '-PublisherDB [$(DatabaseName)] ' +
                  '-Distributor [' + @i_sDistributor + '] ' +
                  '-DistributorSecurityMode 1 ' +
                  '-Continuous'

  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 2
                                            , @step_name = N'Run agent.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'LogReader'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 10
                                            , @retry_interval = 1
                                            , @on_success_step_id = 0
                                            , @on_success_action = 1
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 3
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  set @sCommand = 'sp_MSdetect_nonlogged_shutdown @subsystem = ''LogReader'', ' +
                  '@agent_id = ' + convert(varchar, @nAgentId)
  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 3
                                            , @step_name = N'Detect nonlogged agent shutdown.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'TSQL'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 0
                                            , @retry_interval = 0
                                            , @on_success_step_id = 0
                                            , @on_success_action = 2
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 2
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
  exec @nReturnCode = msdb.dbo.sp_update_job @job_id = @nJobID
                                           , @start_step_id = 1 
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules (job is to run every 5 minutes!)
  EXECUTE @nReturnCode = msdb.dbo.sp_add_schedule @schedule_name = @sSchedName
                                                , @enabled = 1
                                                , @freq_type = 64
                                                , @active_start_date = 20000101
                                                , @active_start_time = 0
                                                , @freq_interval = 0
                                                , @freq_subday_type = 0
                                                , @freq_subday_interval = 0
                                                , @freq_relative_interval = 0
                                                , @freq_recurrence_factor = 0
                                                , @active_end_date = 99991231
                                                , @active_end_time = 235900
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  exec msdb.dbo.sp_attach_schedule @job_name = @i_sJobName,
                                   @schedule_name = @sSchedName



  -- Add the Target Servers
  EXECUTE @nReturnCode = msdb.dbo.sp_add_jobserver @job_id = @nJobID
                                                 , @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  GOTO   EndSave              

QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 

EndSave:
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Create_QueueReader_Job]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  27th August 2010
-- Description:  This procedure Creates the Queue Reader Job for a Transactional 
--               Publication
-- =================================================================================
-- Calls:        none
-- Called By:    usp_REPL_Setup_Replication.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Renamed the procedure 
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Create_QueueReader_Job]
   @i_sJobName     sysname
 , @i_sPublisher   sysname
 , @i_sDistributor sysname
AS
  set nocount on
  
  -- get replication details
  declare @sReplUser   varchar(100)
        , @sSchedName  varchar(100)
        , @nAgentId    int
        , @nJobID      binary(16)  
        , @nReturnCode int    
        , @sCommand    nvarchar(max)
  
  set @sReplUser   = dbo.udf_Get_Parameter_Value('REPL_Admin_User')
  set @sSchedName  = 'Replication agent schedule - ' + @i_sJobName
  set @nAgentId    = ident_current('distribution..MSqReader_agents') + 1
  set @nReturnCode = 0


  
  if @nAgentId is null 
    set @nAgentId = 1
  
  -- create the REPL-Merge category if it doesn't exist
  if not exists (SELECT 1
                 FROM   msdb.dbo.syscategories
                 WHERE  name = N'REPL-QueueReader')
    exec msdb.dbo.sp_add_category @name = N'REPL-QueueReader'

  -- Delete the job with the same name (if it exists)
  SELECT @nJobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE  name = @i_sJobName

  if @nJobID is not null
  begin
    -- Delete the [local] job 
    exec msdb.dbo.sp_delete_job @job_name = @i_sJobName
    select @nJobID = NULL
  end 

  -- Add the job
  exec @nReturnCode = msdb.dbo.sp_add_job @job_id = @nJobID OUTPUT
                                        , @job_name = @i_sJobName
		                                , @owner_login_name = @sReplUser
		                                , @description = N'Reads queues for Queued updating subscriptions.'
		                                , @category_name = N'REPL-QueueReader'
		                                , @enabled = 1
		                                , @notify_level_email = 0
		                                , @notify_level_page = 0
                       	                , @notify_level_netsend = 0
		                                , @notify_level_eventlog = 0
		                                , @delete_level = 0 

  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  set @sCommand = 'sp_MSadd_qReader_history @perfmon_increment = 0, ' +
                  '@agent_id = ' + convert(varchar, @nAgentId) + ', ' +
                  '@runstatus = 1, @comments = ''Starting agent.'''

  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 1
                                            , @step_name = N'Distribution Agent startup message.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'TSQL'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 0
                                            , @retry_interval = 0
                                            , @on_success_step_id = 0
                                            , @on_success_action = 3
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 3
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
  set @sCommand = '-Distributor [' + @i_sDistributor + '] ' +
                  '-DistributionDB [Distribution]' +
                  '-DistributorSecurityMode 1 ' +
                  '-Continuous'

  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 2
                                            , @step_name = N'Run agent.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'QueueReader'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 10
                                            , @retry_interval = 1
                                            , @on_success_step_id = 0
                                            , @on_success_action = 1
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 3
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  set @sCommand = 'sp_MSdetect_nonlogged_shutdown @subsystem = ''QueueReader'', ' +
                  '@agent_id = ' + convert(varchar, @nAgentId)

  exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                            , @step_id = 3
                                            , @step_name = N'Detect nonlogged agent shutdown.'
                                            , @command = @sCommand
                                            , @database_name = N'distribution'
                                            , @server = @i_sPublisher
                                            , @subsystem = N'TSQL'
                                            , @cmdexec_success_code = 0
                                            , @flags = 0
                                            , @retry_attempts = 0
                                            , @retry_interval = 0
                                            , @on_success_step_id = 0
                                            , @on_success_action = 2
                                            , @on_fail_step_id = 0
                                            , @on_fail_action = 2
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
  exec @nReturnCode = msdb.dbo.sp_update_job @job_id = @nJobID
                                           , @start_step_id = 1 
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules (job is to run every 5 minutes!)
  EXECUTE @nReturnCode = msdb.dbo.sp_add_schedule @schedule_name = @sSchedName
                                                , @enabled = 1
                                                , @freq_type = 64
                                                , @active_start_date = 20000101
                                                , @active_start_time = 0
                                                , @freq_interval = 0
                                                , @freq_subday_type = 0
                                                , @freq_subday_interval = 0
                                                , @freq_relative_interval = 0
                                                , @freq_recurrence_factor = 0
                                                , @active_end_date = 99991231
                                                , @active_end_time = 235900
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  exec msdb.dbo.sp_attach_schedule @job_name = @i_sJobName
                                 , @schedule_name = @sSchedName



  -- Add the Target Servers
  EXECUTE @nReturnCode = msdb.dbo.sp_add_jobserver @job_id = @nJobID
                                                 , @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

  GOTO   EndSave              

QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 

EndSave:
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Create_Replicate_Job]...';


GO
-- =================================================================================
-- Author:       ?
-- Create Date:  ?
-- Description:  This creates a Merge Job for a Merge Publication
-- =================================================================================
-- Calls:        none.
-- Called By:    usp_REPL_Add_Merge_Subscription.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  27th August 2010
-- Description:  Procedure altered so that the schedule is set up differently
--               depending on whether it is for CFG or CFG_TRACK tables
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Procedure altered to combine the Merge and Transactional versions
--               and renamed the procedure as 'Merge' was not appropriate for 
--               other methods of replication
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  19th January 2012
-- Description:  Amended Procedure to run on a 5 minute cycle rather than 10 minutes
--               when it is not set to run continuously
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th July 2012
-- Description:  Amended Procedure as there is no longer a distinction between the 
--               Replicate Jobs for Transactional Live and Transactional Config
--               publications
-- =================================================================================
-- Update Author:Chris Jenkins
-- Update Date:  4th September 2012
-- Description:  1) Added code for a new Partitioning publication.  This replicates
--                  the partitioning call on the remote site rather than performing 
--                  all changes on the live site and replicating the changes.  This
--                  involves a large number of deletes and takes a lot of resources
--                  unnecessarily in live environments.
--               2) Tidied and used new SQL 2008 functionality where applicable. 
--               3) Removed the job schedule for transactional replication jobs.
-- =================================================================================
-- Update Author:Chris Jenkins
-- Update Date:  11th September 2012
-- Description:  Added a job schedule for the partitioning publication.  This stops
--               the distribution database getting too big due to the way that the 
--               distribution cleanup job works.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  28th May 2013
-- Description:  Removed the check for the Replication type for the job schedule 
--               creation as this is now required for all jobs.
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Create_Replicate_Job]
   @i_sPubName     sysname
 , @i_sPubType     varchar(15)
 , @i_sJobName     sysname
 , @i_sPublisher   sysname
 , @i_sDistributor sysname
 , @i_sSubscriber  sysname
 , @i_sReplUser    sysname
AS
DECLARE @sSchedName  varchar(100) = 'Replication agent schedule - ' + @i_sJobName
      , @nAgentId    int
      , @nJobID      binary(16)  
      , @nReturnCode int = 0 
      , @sCommand    nvarchar(500)
      , @sCategory   varchar(20)
      , @sSubSystem  varchar(20)
	  , @sDatabaseName varchar(50) = ''

SET NOCOUNT ON
  
IF @i_sPubType = 'Merge'
BEGIN
    set @nAgentId = ident_current('distribution..MSmerge_agents') + 1
    set @sCategory = N'REPL-Merge'
    set @sSubSystem = 'Merge'

	if not exists (SELECT 1
				   FROM   msdb.dbo.syscategories
				   WHERE  name = N'REPL-Merge')
		exec msdb.dbo.sp_add_category @name = N'REPL-Merge'  
END
  
IF @i_sPubType = 'Transactional'
BEGIN
    set @nAgentId = ident_current('distribution..MSdistribution_agents') + 1
    set @sCategory = N'REPL-Distribution'
    set @sSubSystem = 'Distribution'

	if not exists (SELECT 1
				   FROM   msdb.dbo.syscategories
				   WHERE  name = N'REPL-Distribution')
		exec msdb.dbo.sp_add_category @name = N'REPL-Distribution'
END
  
if @nAgentId is null set @nAgentId = 1

-- Delete the job with the same name (if it exists)
SELECT @nJobID = job_id     
FROM   msdb.dbo.sysjobs    
WHERE  name = @i_sJobName

if @nJobID is not null 
begin
	-- Delete the [local] job 
	exec msdb.dbo.sp_delete_job @job_name = @i_sJobName
	select @nJobID = NULL
end 

-- Add the job
exec @nReturnCode = msdb.dbo.sp_add_job @job_id = @nJobID OUTPUT
                                      , @job_name = @i_sJobName
                                      , @owner_login_name = @i_sReplUser
                                      , @description = N'No description available.'
                                      , @category_name = @sCategory
                                      , @enabled = 1
                                      , @notify_level_email = 0
                                      , @notify_level_page = 0
                                      , @notify_level_netsend = 0
                                      , @notify_level_eventlog = 2
                                      , @delete_level= 0
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

-- Add the job steps
IF @i_sPubType = 'Merge'
	set @sCommand = 'sp_MSadd_merge_history @perfmon_increment = 0, ' +
					'@agent_id = ' + convert(varchar, @nAgentId) + ', ' +
					'@runstatus = 1, @comments = ''Starting agent.'''

IF @i_sPubType = 'Transactional'
	set @sCommand = 'sp_MSadd_distribution_history @perfmon_increment = 0, ' +
					'@agent_id = ' + convert(varchar, @nAgentId) + ', ' +
					'@runstatus = 1, @comments = ''Starting agent.'''

exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                          , @step_id = 1
                                          , @step_name = N'Agent startup message.'
                                          , @command = @sCommand
                                          , @database_name = N'distribution'
                                          , @server = @i_sPublisher
                                          , @database_user_name = N''
                                          , @subsystem = N'TSQL'
                                          , @cmdexec_success_code = 0
                                          , @flags = 0
                                          , @retry_attempts = 0
                                          , @retry_interval = 0
                                          , @on_success_step_id = 0
                                          , @on_success_action = 3
                                          , @on_fail_step_id = 0
                                          , @on_fail_action = 3
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
set @sCommand = '-Publisher [' + @i_sPublisher + '] ' +
                '-PublisherDB [$(DatabaseName)] ' +
                '-Publication [' + @i_sPubName + '] ' +
                '-Subscriber [' + @i_sSubscriber + '] ' +
                '-SubscriberDB [$(DatabaseName)] ' +
                '-Distributor [' + @i_sDistributor + '] ' +
                '-DistributorSecurityMode 1 '

IF (@i_sPubType = 'Transactional' AND @i_sPubName != 'Partitioning')
    SET @sCommand = @sCommand + '-Continuous'

exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                          , @step_id = 2
                                          , @step_name = N'Run agent.'
                                          , @command = @sCommand
                                          , @database_name = N'distribution'
                                          , @server = @i_sPublisher
                                          , @database_user_name = N''
                                          , @subsystem = @sSubSystem
                                          , @cmdexec_success_code = 0
                                          , @flags = 0
                                          , @retry_attempts = 10
                                          , @retry_interval = 1
                                          , @on_success_step_id = 0
                                          , @on_success_action = 1
                                          , @on_fail_step_id = 0
                                          , @on_fail_action = 3
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

set @sCommand = 'sp_MSdetect_nonlogged_shutdown @subsystem = ''' + @sSubSystem + ''', ' +
                '@agent_id = ' + convert(varchar, @nAgentId)
                 
exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                          , @step_id = 3
                                          , @step_name = N'Detect nonlogged agent shutdown.'
                                          , @command = @sCommand
                                          , @database_name = N'distribution'
                                          , @server = @i_sPublisher
                                          , @database_user_name = N''
                                          , @subsystem = N'TSQL'
                                          , @cmdexec_success_code = 0
                                          , @flags = 0
                                          , @retry_attempts = 0
                                          , @retry_interval = 0
                                          , @on_success_step_id = 0
                                          , @on_success_action = 2
                                          , @on_fail_step_id = 0
                                          , @on_fail_action = 2
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
 
exec @nReturnCode = msdb.dbo.sp_update_job @job_id = @nJobID 
                                        , @start_step_id = 1 
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

-- Add the job schedules (job is to run every 5 minutes!)
EXECUTE @nReturnCode = msdb.dbo.sp_add_schedule @schedule_name = @sSchedName
												, @enabled = 1
												, @freq_type = 4
												, @active_start_date = 20000101
												, @active_start_time = 000
												, @freq_interval = 1
												, @freq_subday_type = 4
												, @freq_subday_interval = 5
												, @freq_relative_interval = 2
												, @freq_recurrence_factor = 0
												, @active_end_date = 99991231
												, @active_end_time = 235900
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 


exec msdb.dbo.sp_attach_schedule @job_name = @i_sJobName
		                        , @schedule_name = @sSchedName

-- Add the Target Servers
EXECUTE @sCommand = msdb.dbo.sp_add_jobserver @job_id = @nJobID
                                            , @server_name = N'(local)' 
IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 

GOTO   EndSave              

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 

EndSave:
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Create_Snapshot_Job]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  27th August 2010
-- Description:  This procedure Creates the Snapshot Job for a Transactional 
--               Publication
-- =================================================================================
-- Calls:        none
-- Called By:    usp_REPL_TXN_Add_Publication.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Procedure altered to combine the Merge and Transactional versions
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Create_Snapshot_Job]
   @i_sPubName     sysname
 , @i_sPubType     varchar(15)
 , @i_sJobName     sysname
 , @i_sPublisher   sysname
 , @i_sDistributor sysname
 , @i_sReplUser    sysname
AS
  set nocount on
  
  declare @nAgentId  int
        , @nJobID      BINARY(16)  
        , @nReturnCode INT    
        , @sCommand    nvarchar(400)
  
  -- get the next avaiable agent ID for the job
  set @nAgentId = ident_current('distribution..MSsnapshot_agents') + 1
  set @nReturnCode = 0     
  
  if @nAgentId is null 
      set @nAgentId = 1
  
  -- now create the job itself
  BEGIN TRANSACTION
  
  -- check that the REPL_Snapshot category exists
  IF not exists (SELECT 1
                 FROM   msdb.dbo.syscategories
                 WHERE  name = N'REPL-Snapshot')
    EXECUTE msdb.dbo.sp_add_category @name = N'REPL-Snapshot'
  
  -- Delete the job with the same name (if it exists)
  SELECT @nJobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = @i_sJobName)
  
  IF (@nJobID IS NOT NULL)  
  BEGIN  
    exec msdb.dbo.sp_delete_job @job_name = @i_sJobName 

    SELECT @nJobID = NULL
  END 
  
  BEGIN
    -- Add the job
    EXECUTE @nReturnCode = msdb.dbo.sp_add_job @job_id = @nJobID OUTPUT
                                             , @job_name = @i_sJobName
                                             , @owner_login_name = @i_sReplUser
                                             , @description = N'No description available.'
                                             , @category_name = N'REPL-Snapshot'
                                             , @enabled = 1
                                             , @notify_level_email = 0
                                             , @notify_level_page = 0
                                             , @notify_level_netsend = 0
                                             , @notify_level_eventlog = 0
                                             , @delete_level= 0
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
  
    -- Add the job steps
    set @sCommand = 'sp_MSadd_snapshot_history @perfmon_increment = 0,' +
                    ' @agent_id = ' + convert(varchar, @nAgentId) + ',' +
                    ' @runstatus = 1,' +
                    ' @comments = ''Starting agent.'''
  
    exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                              , @step_id = 1
                                              , @step_name = N'Snapshot Agent startup message.'
                                              , @command = @sCommand
                                              , @database_name = N'distribution'
                                              , @server = @i_sPublisher
                                              , @database_user_name = N''
                                              , @subsystem = N'TSQL'
                                              , @cmdexec_success_code = 0
                                              , @flags = 0, @retry_attempts = 0
                                              , @retry_interval = 0
                                              , @output_file_name = N''
                                              , @on_success_step_id = 0
                                              , @on_success_action = 3
                                              , @on_fail_step_id = 0
                                              , @on_fail_action = 3
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 
  
    IF @i_sPubType = 'Merge'
        set @sCommand = N'-Publisher [' + @i_sPublisher + '] -PublisherDB [$(DatabaseName)] ' +
                         '-Distributor [' + @i_sDistributor + '] -Publication [' + @i_sPubName + '] ' +
                         '-ReplicationType 2 -DistributorSecurityMode 1 '

    IF @i_sPubType = 'Transactional'
	    set @sCommand = N'-Publisher [' + @i_sPublisher + '] -PublisherDB [$(DatabaseName)] ' +
		                 '-Distributor [' + @i_sDistributor + '] -Publication [' + @i_sPubName + '] ' +
			             '-DistributorSecurityMode 1 '

    exec @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                              , @step_id = 2
                                              , @step_name = N'Run agent.'
                                              , @command = @sCommand
                                              , @database_name = N'distribution'
                                              , @server = @i_sPublisher
                                              , @database_user_name = N''
                                              , @subsystem = N'Snapshot'
                                              , @cmdexec_success_code = 0
                                              , @flags = 0
                                              , @retry_attempts = 10
                                              , @retry_interval = 1
                                              , @output_file_name = N''
                                              , @on_success_step_id = 0
                                              , @on_success_action = 1
                                              , @on_fail_step_id = 0
                                              , @on_fail_action = 3
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 
  
    set @sCommand = N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Snapshot'',' +
                     ' @agent_id = ' + convert(varchar, @nAgentId)

    EXECUTE @nReturnCode = msdb.dbo.sp_add_jobstep @job_id = @nJobID
                                                 , @step_id = 3
                                                 , @step_name = N'Detect nonlogged agent shutdown.'
                                                 , @command = @sCommand
                                                 , @database_name = N'distribution'
                                                 , @server = @i_sPublisher
                                                 , @database_user_name = N''
                                                 , @subsystem = N'TSQL'
                                                 , @cmdexec_success_code = 0
                                                 , @flags = 0
                                                 , @retry_attempts = 0
                                                 , @retry_interval = 0
                                                 , @output_file_name = N''
                                                 , @on_success_step_id = 0
                                                 , @on_success_action = 2
                                                 , @on_fail_step_id = 0
                                                 , @on_fail_action = 2
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 
  
    EXECUTE @nReturnCode = msdb.dbo.sp_update_job @job_id = @nJobID
                                                , @start_step_id = 1 
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
  
    -- Add the job schedules
    EXECUTE @nReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @nJobID
                                                     , @name = N'Replication agent schedule.'
                                                     , @enabled = 1
                                                     , @freq_type = 1
                                                     , @active_start_date = 20050218
                                                     , @active_start_time = 500
                                                     , @freq_interval = 1
                                                     , @freq_subday_type = 1
                                                     , @freq_subday_interval = 5
                                                     , @freq_relative_interval = 1
                                                     , @freq_recurrence_factor = 0
                                                     , @active_end_date = 99991231
                                                     , @active_end_time = 235959
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback
  
    -- Add the Target Servers
    EXECUTE @nReturnCode = msdb.dbo.sp_add_jobserver @job_id = @nJobID
	                                               , @server_name = N'(local)' 
    IF (@@ERROR <> 0 OR @nReturnCode <> 0) GOTO QuitWithRollback 
  
  END

  COMMIT TRANSACTION
  GOTO EndSave

QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_CreateDefaultData]...';


GO
CREATE PROCEDURE [dbo].[usp_REPL_CreateDefaultData]
AS
BEGIN TRY
    
	IF NOT EXISTS (SELECT 1
	               FROM   SYS_SETTINGS
				   WHERE  [KEY] = 'REPL_Enabled')
	BEGIN
		
		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Enabled'
			  , 'False'
			  , 'The Flag to Specify whether Replication has been implemented')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Admin_User'
			  , '$(REPL_Admin_User)' -- SQLCMD?
			  , 'The SQL Administrator Windows Account used for Replication')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_DB_Datafile_Location' -- SQLCMD?
			  , '$(Distribution_DataPath)\Distribution\Datafile'
			  , 'The Location for the Distribution Database Datafile')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_DB_Logfile_Location' -- SQLCMD?
			  , '$(Distribution_LogPath)\Distribution\Logfile'
			  , 'The Location for the Distribution Database Logfile')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Distributor'
			  , '$(InstanceName)' -- SQLCMD?
			  , 'The Local Machine Instance Name (The Distributor)')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Publisher'
			  , '$(InstanceName)' -- SQLCMD?
			  , 'The Local Machine Instance Name (The Publisher)')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Subscriber'
			  , '$(ResilientInstanceName)' -- SQLCMD?
			  , 'The Resilient Machine Instance Name (The Subscriber)')

		INSERT INTO [dbo].[SYS_SETTINGS] ( [KEY]
										 , VALUE
										 , [DESCRIPTION] )
		VALUES ('REPL_Working_Directory'
			  , '\\$(MachineName)\Replication Data' -- SQLCMD? (could use SQLCMD for primary for first part
			  , 'The unc Directory for Replication Data')
	END
END TRY

BEGIN CATCH
	PRINT ERROR_MESSAGE()

	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
END CATCH
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Drop_Publication]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  19th October 2011
-- Description:  This procedure Drops a Publication. It has replaced the original
--               procedures usp_REPL_Drop_Merge_Publication and 
--               usp_REPL_TXN_Drop_Publication.
-- =================================================================================
-- Calls:        None.
-- Called By:    None.
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  17th January 2012
-- Description:  Amended Procedure to use new Linked Server as the implementation
--               of Replication was causing the Linked Server to not allow remote 
--               Procedure calls for other DBs
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Drop_Publication]
  @i_sPubName    sysname
AS
  declare @sSubscriber    sysname
        , @sPublisher     sysname
        , @sJobName       sysname
        , @nPublications  int
        , @sQReader       sysname
        , @sLogReader     sysname
        , @sPubType       varchar(15)
        
  set @sPublisher  = dbo.udf_Get_Parameter_Value('REPL_Publisher')
  set @sSubscriber = dbo.udf_Get_Parameter_Value('REPL_Subscriber')

  SELECT @sPubType = CASE WHEN publication_type = 0 THEN 'Transactional'
                          WHEN publication_type = 2 THEN 'Merge'
                     END
  FROM   distribution.dbo.MSpublications
  WHERE  publication = @i_sPubName
  AND publisher_db = '$(DatabaseName)'
   
  IF @sPubType = 'Transactional'
  BEGIN
	  SELECT @nPublications = COUNT(*)
	  FROM   distribution.dbo.MSpublications
	  WHERE  publication_type = 0
	  AND publisher_db = '$(DatabaseName)'

	  IF @nPublications = 1
		BEGIN
		  set @sQReader    = '$(DatabaseName) Replication - Transaction Queue Reader'
		  set @sLogReader  = '$(DatabaseName) Replication - Transaction Log Reader'

		  print 'Stopping the Log Reader and Queue Reader Jobs'

		  exec msdb.dbo.sp_stop_job @sLogReader
		  exec msdb.dbo.sp_stop_job @sQReader
		END

	  -- drop the subscription
	  exec sp_dropsubscription @publication = @i_sPubName
							 , @article = 'all'
							 , @subscriber = @sSubscriber
							 , @destination_db = N'$(DatabaseName)'

	  -- cleanup the subscriber
	  exec [REPL-$(ResilientInstanceName)].[$(DatabaseName)].dbo.sp_subscription_cleanup @publisher = @sPublisher -- Need to change the exec statement too
															 , @publisher_db = N'$(DatabaseName)'
															 , @publication = @i_sPubName

	  -- drop the publication
	  exec sp_droppublication @publication = @i_sPubName

  END
  
  if exists (SELECT 1
             FROM   msdb.dbo.sysjobs
             WHERE  name = '$(DatabaseName) Replication - Snapshot ' + @i_sPubName)
  BEGIN
    set @sJobName = '$(DatabaseName) Replication - Snapshot ' + @i_sPubName
    exec msdb.dbo.sp_delete_job @job_name = @sJobName
  END

  if exists (SELECT 1
             FROM   msdb.dbo.sysjobs
             WHERE  name = '$(DatabaseName) Replication - ' + @i_sPubName)
  BEGIN
    set @sJobName = '$(DatabaseName) Replication - ' + @i_sPubName
    exec msdb.dbo.sp_delete_job @job_name = @sJobName
  END
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Remove_Replication]...';


GO
-- =================================================================================
-- Author:       ?
-- Create Date:  ?
-- Description:  This procedure Removes Replication
-- =================================================================================
-- Calls:        None.
-- Called By:    None.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  27th August 2010
-- Description:  Procedure altered as Transactional Replication is used now for 
--               multiple publications so only remove log reader and queue reader at 
--               this stage rather than the drop publication stage. Also tidied the 
--               procedure so that only values required are declared and set.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Amended to use new Job Names that confirm to naming conventions
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th January 2012
-- Description:  Added some checks in to check whether all publications have been
--               dropped before attempting to remove the DB's Replication and also
--               to check whether other DBs are still published for replication
--               before dropping the Distribution DB.
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Remove_Replication]
AS
  set nocount on
  
  -- get replication details
  declare @sSQLStmt     nvarchar(256)
  declare @sSubscriber  sysname
  declare @sPublisher   sysname
  declare @sJobName     sysname
  declare @bRemove      bit
  declare @nPubExists   TINYINT
  declare @nReplDBs     TINYINT

  set @sSubscriber = dbo.udf_Get_Parameter_Value('REPL_Subscriber')
  set @sPublisher  = dbo.udf_Get_Parameter_Value('REPL_Publisher')

  SET @sSQLStmt = 'SELECT @nPubExists = 1'
	 + CHAR(13) + 'FROM   distribution.dbo.MSpublications'
	 + CHAR(13) + 'WHERE  publisher_db = ''$(DatabaseName)'''

  EXECUTE sp_executesql @sSQLStmt, @params = N'@nPubExists TINYINT OUTPUT', @nPubExists = @nPubExists OUTPUT

  IF @nPubExists IS NULL
      SET @bRemove = 'True'
  ELSE 
      SET @bRemove = 'False'
	  
  IF @bRemove = 'True'
  BEGIN 
	  if exists (SELECT 1
				 FROM   msdb.dbo.sysjobs
				 WHERE  name = '$(DatabaseName) Replication - Transaction Log Reader')
	  BEGIN
		set @sJobName = '$(DatabaseName) Replication - Transaction Log Reader'
		exec msdb.dbo.sp_delete_job @job_name = @sJobName
	  END

	  if exists (SELECT 1
				 FROM   msdb.dbo.sysjobs
				 WHERE  name = '$(DatabaseName) Replication - Transaction Queue Reader')
	  BEGIN
		set @sJobName = '$(DatabaseName) Replication - Transaction Queue Reader'
		exec msdb.dbo.sp_delete_job @job_name = @sJobName
	  END

	  -- Remove the merge replication option from the database
	  exec sp_replicationdboption @dbname = N'$(DatabaseName)'
								, @optname = N'merge publish'
								, @value = N'false'

	  exec sp_replicationdboption @dbname = N'$(DatabaseName)'
								, @optname = N'Publish'
								, @value = N'false'

	  -- update SYS_Settings
	  update SYS_SETTINGS
	  set value = 'false'
	  where [KEY] = 'REPL_Enabled'

	  SET @sSQLStmt = 'SELECT @nREPLDBs = 1'
		 + CHAR(13) + 'FROM   sys.Databases'
		 + CHAR(13) + 'WHERE  is_published = ''True'''
		 + CHAR(13) + 'OR     is_merge_published = ''True'''

	  EXECUTE sp_executesql @sSQLStmt, @params = N'@nREPLDBs TINYINT OUTPUT', @nREPLDBs = @nREPLDBs OUTPUT

	  IF @nREPLDBs IS NULL
		  SET @bRemove = 'True'
	  ELSE 
		  SET @bRemove = 'False'
	  
	  IF @bRemove = 'True'
	  BEGIN 
		  -- Unregister the Subscriber
		  exec sp_dropsubscriber @subscriber = @sSubscriber,
								 @reserved = N'drop_subscriptions'

		  -- now drop the distribution database
		  exec sp_dropdistpublisher @publisher = @sPublisher
								  , @no_checks = 1
		  exec sp_dropdistributiondb @database = 'distribution'
		  exec sp_dropdistributor @no_checks = 1
	  END
	  ELSE
		  PRINT 'Cannot completely remove replication as databases are still published for replication'

  END
  ELSE
      PRINT 'Publications Still Exist for Reactor therefore Replication cannot be removed'
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Setup_Replication]...';


GO
-- =================================================================================
-- Author:       ?
-- Create Date:  ?
-- Description:  This procedure Sets up Replication so that it can be implemented
-- =================================================================================
-- Calls:        usp_REPL_CreateDefaultData
--				
-- Called By:    None.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  11th January 2010
-- Description:  Procedure amended so that Transactional Replication is enabled as
--               well as Merge Replication
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  27th August 2010
-- Description:  Procedure altered as Transactional Replication is used now for 
--               multiple publications and also tidied the procedure so that only
--               values required are declared and set.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  23rd August 2011
-- Description:  Removed the Hardcoded Distributor_Admin password as a) this should 
--               not be set in the scripts and b) this should not be needed as only
--               needs to be set for remote distributors
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  19th October 2011
-- Description:  Amended Procedure to rename the Log and Queue Reader Jobs and
--               to create default Replication Data if not present at initialisation
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  25th October 2011
-- Description:  Added the configuration of the maximum replication text size as  
--               this was set too small to cope with varchar(max) columns
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th January 2012
-- Description:  Added a check to see if the Distrubution Database has already been 
--               defined on this server.  If it has then it does not attempt to 
--               redefine it.  Also reordered the procedure to carry out the tasks
--               in a sensible order.
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Update Date:  13th June 2012
-- Description:  Amended Procedure to use a SQLCMD Variable for the Distribution 
--               Database Size as the default sizes are so small that it causes
--               disk fragmentation
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Setup_Replication]
AS

set nocount on

  -- get replication details
  declare @sDistributor        sysname
  declare @sPublisher          sysname
  declare @sSubscriber         sysname
  declare @sLogreader          varchar(100)
  declare @sQreader            varchar(100)
  declare @sDatafileLocation   varchar(500)
  declare @sLogfileLocation    varchar(500)
  declare @sWorkingDir         varchar(500)
  
  EXEC usp_REPL_CreateDefaultData

  exec sp_configure 'max text repl size', 2147483647
  RECONFIGURE

  set @sDistributor       = dbo.udf_Get_Parameter_Value('REPL_Distributor')
  set @sPublisher         = dbo.udf_Get_Parameter_Value('REPL_Publisher')
  set @sSubscriber        = dbo.udf_Get_Parameter_Value('REPL_Subscriber')
  set @sDatafileLocation  = dbo.udf_Get_Parameter_Value('REPL_DB_Datafile_Location')
  set @sLogfileLocation   = dbo.udf_Get_Parameter_Value('REPL_DB_Logfile_Location')
  set @sWorkingDir        = dbo.udf_Get_Parameter_Value('REPL_Working_Directory')

  IF NOT EXISTS (SELECT 1
                 FROM   sys.databases
                 WHERE  name = 'Distribution')
  BEGIN
	  -- create the distributor_admin user, password as specified
	  exec master.dbo.sp_adddistributor  @distributor = @sDistributor

	  -- Updating the agent profile defaults
	  exec sp_MSupdate_agenttype_default @profile_id = 1
	  exec sp_MSupdate_agenttype_default @profile_id = 2
	  exec sp_MSupdate_agenttype_default @profile_id = 4
	  exec sp_MSupdate_agenttype_default @profile_id = 6
	  exec sp_MSupdate_agenttype_default @profile_id = 11
  
	  -- Adding the distribution database
	  exec sp_adddistributiondb @database = N'distribution',
								@data_folder = @sDatafileLocation, 
								@data_file = N'distribution.MDF', 
								@data_file_size = '5120',
								@log_folder = @sLogfileLocation, 
								@log_file = N'distribution.LDF', 
								@log_file_size = '5120', 
								@min_distretention = 0, 
								@max_distretention = 72, 
								@history_retention = 48, 
								@security_mode = 1

	   -- Adding the distribution publisher
	  exec sp_adddistpublisher  @publisher = @sPublisher,
								@distribution_db = N'distribution', 
								@security_mode = 1,
								@working_directory = @sWorkingDir, 
								@trusted = N'false', 
								@thirdparty_flag = 0,
								@publisher_type = N'MSSQLSERVER'
  END
  

   -- Enable Replication on the database
  exec sp_replicationdboption @dbname = N'$(DatabaseName)', -- SQLCMD for Config DB Name?
                              @optname = N'publish',
                              @value = N'true'

  exec sp_replicationdboption @dbname = N'$(DatabaseName)', -- SQLCMD for Config DB Name?
                              @optname = N'merge publish',
                              @value = N'true'

  -- update SYS_Settings
  update SYS_Settings
  set [Value] = 'true'
  where [Key] = 'REPL_Enabled'

  set  @sLogreader = '$(DatabaseName) Replication - Transaction Log Reader' -- SQLCMD
  set  @sQreader = '$(DatabaseName) Replication - Transaction Queue Reader'  -- SQLCMD

  if not exists (SELECT 1
                 FROM   msdb.dbo.sysjobs
                 WHERE  name = '$(DatabaseName) Replication - Transaction Log Reader') -- SQLCMD
      -- Create the Log Reacer job for this distributor.
      exec usp_REPL_Create_LogReader_Job @i_sJobName     = @sLogreader
                                       , @i_sPublisher   = @sPublisher
                                       , @i_sDistributor = @sDistributor

  if not exists (SELECT 1
                 FROM   msdb.dbo.sysjobs
                 WHERE  name = '$(DatabaseName) Replication - Transaction Queue Reader') -- SQLCMD
	  -- Create the Queue Reader job for this distributor.
	  exec usp_REPL_Create_QueueReader_Job @i_sJobName   = @sQreader
										 , @i_sPublisher   = @sPublisher
										 , @i_sDistributor = @sDistributor

  -- now create the Log Reader Agent
  exec sp_addlogreader_agent @job_name = @sLogreader,
                             @job_login = null,
                             @job_password = null,
                             @publisher_security_mode = 1

  exec msdb.dbo.sp_start_job @sLogreader

  -- now create the Queue Reader Agent
  exec sp_addqreader_agent @job_name = @sQreader,
                           @job_login = null, 
                           @job_password = null, 
                           @frompublisher = 1

  exec msdb.dbo.sp_start_job @sQreader
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Start_Job]...';


GO
-- =================================================================================
-- Author:       ?
-- Create Date:  ?
-- Description:  This procedure starts a job based on the passed in job name and
--               waits for the job to finish if @i_nWaitForFinish is set to True
--               before returning to the calling procedure
-- =================================================================================
-- Calls:        None.
-- Called By:    usp_REPL_Setup_Replication
--               usp_REPL_TXN_Create_Live_Publication
--               usp_REPL_Create_CFG_Merge_Publication
--               usp_REPL_TXN_Create_Programmability_Publication
--               usp_REPL_TXN_Create_CFG_Publication
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  25th August 2011
-- Description:  Modified to not loop every second waiting for the job to start as
--               this causes problems with jobs that start and finish before the
--               first check.  We now just wait 5 seconds before checking whether
--               job has finished.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th January 2012
-- Description:  Amended Procedure to accept an input parameter of step name and
--               start the job at the specified step if not NULL
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Start_Job]
  @i_sJobName         sysname,
  @i_nWaitForFinish   varchar(5) = 'true',
  @i_sStepName        sysname = NULL
AS
  set nocount on

  declare @JobId  binary(16)
  declare @JobName   varchar(80)
  declare @JobStatus int

  set @JobName = @i_sJobName

  select @JobId = job_id
  from msdb.dbo.sysjobs
  where name = @jobname

  -- manually start the job
  exec msdb.dbo.sp_start_job @Job_Id = @JobId, @step_name=@i_sStepName

  -- now wait for job to start
  WAITFOR DELAY '00:00:05'

  if @i_nWaitForFinish = 'true' begin
    -- then for it to finish
    WAITFOR DELAY '00:00:02'
  
    while 1=1 begin
      EXEC @JobStatus = usp_get_job_status @JobName
      IF @JobStatus = 4 BREAK
  
      WAITFOR DELAY '00:00:02'
    end
  end
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Add_Publication]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  19th October 2011
-- Description:  This procedure creates a Publication and it's associated snapshot 
--               job if necessary. It has replaced the original procedures
--               usp_REPL_Add_Merge_Publication and usp_REPL_TXN_Add_Publication.
-- =================================================================================
-- Calls:        usp_REPL_Create_Snapshot_Job
-- Called By:    usp_REPL_Create_Publication_Live_Transactional
--               usp_REPL_Create_Publication_CFG_Transactional
--               usp_REPL_Create_Publication_CFG_Merge
--               usp_REPL_Recreate_Publication_Live_Transactional
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th July 2012
-- Description:  Amended Procedure as there is no longer a distinction between the 
--               Replicate Jobs for Transactional Live and Transactional Config
--               publications
-- =================================================================================
-- Update Author:Chris Jenkins
-- Update Date:  3rd September 2012
-- Description:  1) Altered to set @immediate_sync to false for transactional 
--                  replication.  This stops with distribution holding a full 
--                  history of all data for the retention period.  Now it only holds   
--                  everything that has not been synchronised.
--               2) Added code for a new Partitioning publication.  This replicates
--                  the partitioning call on the remote site rather than performing 
--                  all changes on the live site and replicating the changes.  This
--                  involves a large number of deletes and takes a lot of resources
--                  unnecessarily in live environments.
--               3) Set live table publications not to replicate ddl changes.  These
--                  are now managed by the new Partitioning publication.
--               4) Tidied and used new SQL 2008 functionality where applicable. 
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Add_Publication] 
   @i_sPubName     sysname
 , @i_sPubType     VARCHAR(15)
 , @i_sJobName     sysname = NULL
 , @i_sPublisher   sysname 
 , @i_sDistributor sysname
 , @i_sReplUser    sysname
 , @i_bSyncPub     bit
AS
DECLARE @sLogreader   sysname = '$(DatabaseName) Replication - Transaction Log Reader' --SQLCMD?
      , @sQreader     sysname = '$(DatabaseName) Replication - Transaction Queue Reader' --SQLCMD?
      , @sDescription varchar(100) = N'$(DatabaseName) ' + @i_sPubType + ' publication of ' + @i_sPubName --SQLCMD?

SET NOCOUNT ON

IF @i_bSyncPub = 'True'
BEGIN
	-- Create the snapshot job for this publication.
	EXEC usp_REPL_Create_Snapshot_Job @i_sPubName = @i_sPubName
		                            , @i_sPubType = @i_sPubType
		  			     			, @i_sJobName = @i_sJobName
									, @i_sPublisher = @i_sPublisher
									, @i_sDistributor = @i_sDistributor
									, @i_sReplUser = @i_sReplUser

END

if @i_sPubType = 'Transactional'
BEGIN
	-- now create the publication
	IF (@i_sPubName = 'Partitioning')
	BEGIN
		EXEC sp_addpublication @publication = @i_sPubName
							 , @restricted = N'false'
							 , @sync_method = N'concurrent'
							 , @repl_freq = N'continuous'
							 , @description = @sDescription
							 , @status = N'active'
							 , @allow_push = N'true'
							 , @allow_pull = N'true'
							 , @allow_anonymous = N'false'
							 , @enabled_for_internet = N'false'
							 , @independent_agent = N'true'
							 , @immediate_sync = N'false'
							 , @allow_sync_tran = N'false'
							 , @replicate_ddl = 1 
							 , @logreader_job_name = @sLogReader
							 , @qreader_job_name = @sQreader
	END
	ELSE
	BEGIN
		EXEC sp_addpublication @publication = @i_sPubName
							 , @restricted = N'false'
							 , @sync_method = N'concurrent'
							 , @repl_freq = N'continuous'
							 , @description = @sDescription
							 , @status = N'active'
							 , @allow_push = N'true'
							 , @allow_pull = N'true'
							 , @allow_anonymous = N'false'
							 , @enabled_for_internet = N'false'
							 , @independent_agent = N'true'
							 , @immediate_sync = N'false'
							 , @allow_sync_tran = N'false'
							 , @autogen_sync_procs = N'false'
							 , @replicate_ddl = 0 
							 , @logreader_job_name = @sLogReader
							 , @qreader_job_name = @sQreader
	END
END
  

  
-- todo, expand to include ALL current log ins
EXEC sp_grant_publication_access @publication = @i_sPubName
	                           , @login = @i_sReplUser
EXEC sp_grant_publication_access @publication = @i_sPubName
	                           , @login = N'sa' -- JL: change this to be SQLCMD var?
EXEC sp_grant_publication_access @publication = @i_sPubName
	                           , @login = N'distributor_admin'

IF @i_bSyncPub = 'True'
BEGIN
	IF (@i_sPubName = 'Partitioning')
	BEGIN
		EXEC sp_addpublication_snapshot @publication = @i_sPubName
                                      , @frequency_type = 1
                                      , @frequency_interval = 1
                                      , @frequency_relative_interval = 1
                                      , @frequency_recurrence_factor = 0
                                      , @frequency_subday = 8
                                      , @frequency_subday_interval = 1
                                      , @active_start_time_of_day = 0
                                      , @active_end_time_of_day = 235959
                                      , @active_start_date = 0
                                      , @active_end_date = 0
                                      , @job_login = null
                                      , @job_password = null
                                      , @publisher_security_mode = 1
	END
	ELSE
	BEGIN
		EXEC sp_addpublication_snapshot @publication = @i_sPubName
		    						  , @frequency_type = 8
									  , @frequency_interval = 64
									  , @frequency_relative_interval = 0
									  , @frequency_recurrence_factor = 1
									  , @frequency_subday = 1
									  , @frequency_subday_interval = 0
									  , @active_start_date = 0
									  , @active_end_date = 0
									  , @active_start_time_of_day = 5200
									  , @active_end_time_of_day = 0
									  , @snapshot_job_name = @i_sJobName
									  , @job_login = null
									  , @job_password = null
									  , @publisher_security_mode = 1
	END
END
GO
PRINT N'Creating Procedure [dbo].[usp_REPL_Add_Subscription]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  19th October 2011
-- Description:  This procedure creates a Subscription to a publication and creates 
--               the replication job. It has replaced the original procedures
--               usp_REPL_Add_Merge_Subscription and usp_REPL_TXN_Add_Subscription.
-- =================================================================================
-- Calls:        usp_REPL_Create_Replicate_Job
-- Called By:    usp_REPL_Create_Publication_Live_Transactional
--               usp_REPL_Create_Publication_CFG_Transactional
--               usp_REPL_Create_Publication_CFG_Merge
--               usp_REPL_Recreate_Publication_Live_Transactional
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th July 2012
-- Description:  Amended Procedure as there is no longer a distinction between the 
--               Replicate Jobs for Transactional Live and Transactional Config
--               publications
-- =================================================================================
-- Update Author:Chris Jenkins
-- Update Date:  3rd September 2012
-- Description:  1) Added code for a new Partitioning publication.  This replicates
--                  the partitioning call on the remote site rather than performing 
--                  all changes on the live site and replicating the changes.  This
--                  involves a large number of deletes and takes a lot of resources
--                  unnecessarily in live environments.
--               2) Tidied and used new SQL 2008 functionality where applicable. 
-- =================================================================================
-- Update Author:Chris Jenkins
-- Date:         13th September 2012
-- Description:  Altered to remove the automatic synchronisation of the Partitioning
--               publication.  This now uses the same synchronisation as the live 
--               table publications.
-- *Note:        The 'none' @sync_type is deprecated.  We should be using 
--               the 'replication support only' option.  However this will need 
--               testing and is outside of the scope of the current work.
--               http://msdn.microsoft.com/en-us/library/ms181702.aspx
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Add_Subscription]
   @i_sPubName     sysname
 , @i_sPubType     varchar(15)
 , @i_sJobName     sysname
 , @i_sPublisher   sysname
 , @i_sDistributor sysname
 , @i_sSubscriber  sysname
 , @i_sReplUser    sysname
 , @i_bSyncPub     bit 
AS
SET NOCOUNT ON

DECLARE @sDescription varchar(100) = N'$(DatabaseName) ' + @i_sPubType + ' publication of ' + @i_sPubName
       ,@nFrequency   tinyint = 1 --Once

IF (@i_sPubType = 'Transactional' AND @i_sPubName != 'Partitioning')
	SET @nFrequency = 64 -- Autostart

-- manually add the merge job otherwise a system name is defaulted
EXEC usp_REPL_Create_Replicate_Job @i_sPubName = @i_sPubName
                                 , @i_sJobName = @i_sJobName
                                 , @i_sPublisher = @i_sPublisher
                                 , @i_sDistributor = @i_sDistributor
                                 , @i_sSubscriber = @i_sSubscriber
                                 , @i_sReplUser = @i_sReplUser
                                 , @i_sPubType = @i_sPubType

IF @i_sPubType = 'Merge'
BEGIN
	EXEC sp_addmergesubscription @publication = @i_sPubName
							   , @subscriber = @i_sSubscriber
							   , @subscriber_db = N'$(DatabaseName)' --SQLCMD?
							   , @subscription_type = N'push'
							   , @sync_type = N'automatic'
							   , @subscriber_type = N'Global'
							   , @subscription_priority = 75
							   , @description = null
							   , @use_interactive_resolver = N'false'
							   , @merge_job_name = @i_sJobName

	EXEC sp_addmergepushsubscription_agent @publication = @i_sPubName
										 , @subscriber = @i_sSubscriber
										 , @subscriber_db = N'$(DatabaseName)' --SQLCMD?
										 , @job_login = null
										 , @job_password = null
										 , @job_name = @i_sJobName
										 , @subscriber_security_mode = 1
										 , @publisher_security_mode = 1
										 , @frequency_type = 1
										 , @frequency_interval = 0
										 , @frequency_relative_interval = 0
										 , @frequency_recurrence_factor = 0
										 , @frequency_subday = 0
										 , @frequency_subday_interval = 0
										 , @active_start_time_of_day = 0
										 , @active_end_time_of_day = 0
										 , @active_start_date = 0
										 , @active_end_date = 19950101
										 , @enabled_for_syncmgr = N'False'
END
  
IF @i_sPubType = 'Transactional'
BEGIN
	-- now add the push subscription
	EXEC sp_addsubscription @publication = @i_sPubName
						  , @subscriber = @i_sSubscriber
						  , @destination_db = N'$(DatabaseName)' --SQLCMD?
						  , @sync_type = N'none'
						  , @article = N'all'
						  , @update_mode = N'read only'
						  , @loopback_detection = 'true'

	EXEC sp_addpushsubscription_agent @publication = @i_sPubName
									, @subscriber = @i_sSubscriber
									, @subscriber_db = N'$(DatabaseName)' --SQLCMD?
									, @job_login = null
									, @job_password = null
									, @job_name = @i_sJobName
									, @subscriber_security_mode = 1
									, @frequency_type = @nFrequency
									, @frequency_interval = 0
									, @frequency_relative_interval = 0
									, @frequency_recurrence_factor = 0
									, @frequency_subday = 0
									, @frequency_subday_interval = 0
									, @active_start_time_of_day = 0
									, @active_end_time_of_day = 0
									, @active_start_date = 0
									, @active_end_date = 19950101
									, @enabled_for_syncmgr = N'False'
END
GO
																   


  

																					
				  
				  
																
																					
									 
										 
										  
								   
					  
																					
								   
								 
																					
													 
																					
								   
								  
																				  
																					
																					
								   
								  
															  
																					
							  
							  
																			   
																	 
																					
								   
							  
																					 
											
																					
								   
							   
																					 
																					
																				
																				 
						  
																					
							  
								   
																				 
																				 
																				 
																
																					
								   
								
																					 
																					
											   
																					
							  
							   
															 
																					
															  
  
					
									
									
									
									
									
									
										 
								  
								

						   
																   
																	 
																	
																	
																   
							 
							
  
																	   

							  
													   
													
													   
														   
															   
														 
								  

							  
							   
			  
					  
												 
												   
												 
																					 
				   
  
				 
  
				 
										   
  
								
  
														
													 
												  
													   
													   
	 
  
				  
					   

																   
												 
												 
  
																   
															  
														
														
															
																
															  
														  
														
														

									
												 
												 
  
PRINT N'Creating Procedure [dbo].[usp_REPL_Create_Publication_CFG_Transactional]...';


GO
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  19th April 2011
-- Description:  This procedure Implements Transactional Replication for 
--               Specified Config Tables to allow continuous Replication
-- =================================================================================
-- Calls:        usp_REPL_Start_Job
--             , usp_REPL_Add_Publication
--             , usp_REPL_Add_Subscription
--             , usp_REPL_Add_Article
-- Called By:    None.
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th October 2011
-- Description:  Renamed the procedure and amended to call new combined procedures
--               also amended to create Job Names that confirm to naming conventions
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  31st October 2011
-- Description:  Amended Procedure to reflect change of Linked Server from Machine
--               Name to Instance Name
-- =================================================================================
-- Author:       Donna-Marie Dimmer
-- Create Date:  17th January 2012
-- Description:  Amended Procedure to use new Linked Server as the implementation
--               of Replication was causing the Linked Server to not allow remote 
--               Procedure calls for other DBs
-- =================================================================================
-- Update Author:Donna-Marie Dimmer
-- Update Date:  20th July 2012
-- Description:  Amended Procedure to remove unnecessary parameters and add new ones 
--               to cater for the amendment to the Replication process to allow for 
--               all Transactional publications to replicate in the same way and
--               also for the partitioned live table publications to not generate
--               snapshots
-- =================================================================================
-- Update Author:Joe Lees
-- Update Date:  5th August 2015
-- Description:  Added 'OM_SNMP_GET_DETAILS' to the list of table to replicate.
-- =================================================================================
CREATE PROCEDURE [dbo].[usp_REPL_Create_Publication_CFG_Transactional] AS
  declare @sJobname          sysname
        , @sPubName          sysname
        , @sTable            sysname
        , @sPublisher        sysname
        , @sDistributor      sysname
        , @sSubscriber       sysname
        , @sReplUser         varchar(100)
        , @sViewLike         varchar(50)
        , @sType             varchar(5)
        , @sPublicationFound int
        , @bReplicated       bit
		, @sPubType          varchar(15)
		, @sSQLStmt          nvarchar(256)
		, @nPubExists        TINYINT
		, @bSyncPub          bit -- syncPub?

  set @sPublisher   = dbo.udf_Get_Parameter_Value('REPL_Publisher')
  set @sDistributor = dbo.udf_Get_Parameter_Value('REPL_Distributor')
  set @sSubscriber  = dbo.udf_Get_Parameter_Value('REPL_Subscriber')
  set @sReplUser    = dbo.udf_Get_Parameter_Value('REPL_Admin_User')
  set @bSyncPub     = 'True' -- syncPub?
  set @sPubType     = 'Transactional'
  set @sPubName = 'All Tables' -- Should change PubName

  SELECT @bReplicated = is_published
  FROM   [REPL-$(ResilientInstanceName)].master.sys.databases -- SQLCMD vars needed here: $(REPL_Publisher)
  WHERE  name = '$(DatabaseName)' 

    -- check if the publication exists
  exec sp_helppublication @sPubName
                        , @sPublicationFound output

  if @sPublicationFound <> 0 
      goto finish

  /* REMOVE ALL THIS?
  IF @bReplicated = 'True'
  BEGIN
	  SET @sSQLStmt = 'SELECT @nPubExists = 1'
		 + CHAR(13) + 'FROM   [REPL-V-VFDB1].distribution.dbo.MSpublications' -- We need to replace with SQLCMD here...
		 + CHAR(13) + 'WHERE  publication = ''' + @sPubName + ''''
		 + CHAR(13) + 'AND    publisher_db = ''Reactor'''

	  EXECUTE sp_executesql @sSQLStmt, @params = N'@nPubExists TINYINT OUTPUT', @nPubExists = @nPubExists OUTPUT

	  IF @nPubExists IS NOT NULL
		  set @bSyncPub = 'False'
  END
  */

  set @sJobName = '$(DatabaseName) Replication - Snapshot ' + @sPubName
  


    -- Add the Transactional publication
  exec usp_REPL_Add_Publication @i_sPubName = @sPubName
	                          , @i_sPubType = @sPubType
                              , @i_sJobName = @sJobName
                              , @i_sPublisher = @sPublisher
                              , @i_sDistributor = @sDistributor
                              , @i_sReplUser = @sReplUser
                              , @i_bSyncPub = @bSyncPub -- syncPub?


  declare c_get_tabs cursor for
    select a.name
    from   sysobjects a 
    where  ObjectProperty(id, 'IsUserTable') = 1
    and    ObjectProperty(id, 'IsSystemTable') = 0
    and    ObjectProperty(id, 'IsMSShipped') = 0
	and  name <> 'sys_settings'
	--in ('Areas', 'Customers', 'Environments', 'Cust_Area_Mapped', 'Config_Parameters', 'Config_Mapping', 'Config_Values', 'DeleteTransactions')
	-- not = sys_settings

    open c_get_tabs
  
    while 1 = 1 begin
      fetch next from c_get_tabs into @sTable
      if @@fetch_status <> 0 break
  
      exec usp_REPL_Add_Article @i_sPubName = @sPubName
	                          , @i_sPubType = @sPubType -- pubType?
                              , @i_sArticleName = @sTable
                              , @i_sArticleType = 'table'
      end
  
    close c_get_tabs
    deallocate c_get_tabs

    -- create the merge job on the publisher (push to the subscriber)
    set @sJobName = '$(DatabaseName) Replication - ' + @sPubName
    exec usp_REPL_Add_Subscription @i_sPubName = @sPubName
                                 , @i_sPubType = @sPubType
                                 , @i_sJobName = @sJobName
                                 , @i_sPublisher = @sPublisher
                                 , @i_sDistributor = @sDistributor
                                 , @i_sSubscriber = @sSubscriber
                                 , @i_sReplUser = @sReplUser
                                 , @i_bSyncPub = @bSyncPub
	set @sJobname = '$(DatabaseName) Replication - ' + @sPubName  
    exec usp_REPL_Start_Job @i_sJobName = @sJobName
	                      , @i_nWaitForFinish = 'False'
/*
    IF @bSyncPub = 'True' -- ???
      BEGIN
        set @sJobName = '$(DatabaseName) Replication - Snapshot ' + @sPubName
        exec usp_REPL_Start_Job @i_sJobName = @sJobName
	                      , @i_nWaitForFinish = 'True'
      END
      
    set @sJobname = '$(DatabaseName) Replication - ' + @sPubName  
    exec usp_REPL_Start_Job @i_sJobName = @sJobName
	                      , @i_nWaitForFinish = 'False'
  */

  finish:
  return 0
GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
-- =================================================================================
-- Update Author:Joe Lees
-- Update Date:  28/02/2019
-- Description:  Adds relevant Config Items to required SYS_ tables
-- Execute the Procedure to create partitioned tables
-- =================================================================================

							 
										 

																
--IF NOT EXISTS (SELECT 1 FROM SYS_SETTINGS WHERE [KEY] = 'Partitioned_Table_Suffix_Length')
--BEGIN
--	INSERT INTO SYS_SETTINGS ([KEY], [VALUE])
--	VALUES ('Partitioned_Table_Suffix_Length', '3')

											   
																						  
																						  
																						
--END

--DECLARE @partitionThreshold int
--SET @partitionThreshold = $(retentionLengthHours) * 2

-- should we first truncate in case? / if exists?
-- insert data from SQLCMD Vars
--INSERT INTO SYS_PART_Config (View_Name, Partitioning_Column, No_Of_Live_Partitions, Live_Partition_Lifetime_Hours, Live_Partition_Threshold_Hours, No_Of_Historical_Partitions, Fileset)
--VALUES	('EventLog', 'EventTime', $(numPartitions), $(retentionLengthHours), @partitionThreshold, 0, 'PRIMARY'), 
--		('ProcessLog', 'ProcessStartTime', $(numPartitions), $(retentionLengthHours), @partitionThreshold, 0, 'PRIMARY'),
--		('StageLog', 'StageStartTime', $(numPartitions), $(retentionLengthHours), @partitionThreshold, 0, 'PRIMARY'),
--		('ParameterLog', 'InsertTime', $(numPartitions), $(retentionLengthHours), @partitionThreshold, 0, 'PRIMARY')

--INSERT INTO SYS_PART_Views (Partitioned_View_Name, Master_View_Name)
--VALUES	('EventLog', 'EventLog'),
--		('ProcessLog', 'ProcessLog'),
--		('StageLog', 'StageLog'),
--		('ParameterLog', 'ParameterLog')

-- Call procedure to create the Partitioned Tables
-- exec usp_PART_CreatePartition

-- Create the scheduled job
--:r .\Jobs\MaintainPartitions.sql 
--exec msdb.dbo.sp_start_job @job_name = '$(DatabaseName) Partitioning'



USE [$(DatabaseName)];
-- Create Roles
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_executor')
	CREATE ROLE [db_executor] AUTHORIZATION [dbo] 

--Add the users to the db and grant privileges where necessary
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'$(CortexDBUser)')
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$(CortexDBUser)')
		CREATE USER [$(CortexDBUser)] FOR LOGIN [$(CortexDBUser)];

	GRANT CONNECT TO [$(CortexDBUser)] AS [dbo];
	EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'$(CortexDBUser)'
	EXECUTE sp_addrolemember @rolename = N'db_datawriter', @membername = N'$(CortexDBUser)'
	EXECUTE sp_addrolemember @rolename = N'db_executor', @membername = N'$(CortexDBUser)'
END 

-- Give the DB User permissions to call the procedures
GRANT EXECUTE ON [dbo].[usp_InsertUpdate_ParamValues] TO [$(CortexDBUser)];

USE [$(DatabaseName)]
GO

SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

	PRINT 'Merging into SYS_Sequence'

	MERGE [dbo].[SYS_Sequence] AS TARGET
	USING (VALUES ('Areas','ID', 1)
				, ('Customers','ID',1)
				, ('Environments','ID',1)
				, ('Param_Name','ID',1)
				, ('Param_Values','ID',1)
				, ('ADGroup','ID',1)
				, ('AreaADGroupsLink','ID',1))
		   AS SOURCE ([TableName]
					 ,[ColumnName]
					 ,[SequenceNumber])
	   ON TARGET.[TableName] = SOURCE.[TableName] AND TARGET.[ColumnName] = SOURCE.[ColumnName]
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ([TableName], [ColumnName], [SequenceNumber])
		VALUES ([TableName], [ColumnName], [SequenceNumber]);

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
	BEGIN
		ROLLBACK TRANSACTION
	END

	DECLARE @sErrorMessage  NVARCHAR(4000)
		  , @nErrorSeverity INT
		  , @nErrorState    INT;

	SELECT @sErrorMessage  = ERROR_MESSAGE()
		 , @nErrorSeverity = ERROR_SEVERITY()
		 , @nErrorState    = ERROR_STATE();

	-- Use RAISERROR inside the CATCH block to return 
	-- error information about the original error that 
	-- caused execution to jump to the CATCH block.
	RAISERROR (@sErrorMessage  -- Message text.
			 , @nErrorSeverity -- Severity.
			 , @nErrorState);  -- State.
END CATCH


GO

GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END


GO
PRINT N'Update complete.';


GO