Telligent Community 6.1 SQL Install - Non DBO Schema

README.md

The following tables don't have schema specified, meaning they will be created with the default schema of the user you are executing the script with, not dbo. If this happens errors will be logged in the SQL messages window and the objects will be created but with the wrong schema, to fix this delete the objects and run the script(s) below:

Telligent Community 6.1 Full Install

These are fixed with the CreateWithSchema.sql script

View: te_vw_ModerationAction Stored Procedure: cs_SiteTerms_ResetAllToDefault Function: GetProperties Table: cs_Messaging_MessageReplies

Telligent Community 6.1 Hotfix Install (6.1.24.39307)

This can be fixed with the HotfixCreateWithSchema.sql script

Stored Procedure: te_DistributedMonitor_Leases_Remove

CreateWithSchema.sql

Print 'Creating...te_vw_ModerationAction'

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[te_vw_ModerationAction]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[te_vw_ModerationAction]
GO

create view [dbo].[te_vw_ModerationAction]
as
select ActionId as ModerationAction, Description, TotalActions, 1000 as SettingsID
from te_Audit_Actions
GO

SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
GO


GRANT SELECT ON [dbo].[te_vw_ModerationAction] TO public
GO

Print 'Creating...cs_SiteTerms_ResetAllToDefault'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[cs_SiteTerms_ResetAllToDefault]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[cs_SiteTerms_ResetAllToDefault]
GO

CREATE PROCEDURE [dbo].[cs_SiteTerms_ResetAllToDefault]
(
	@Language	NCHAR(5)
)
AS
	UPDATE	terms
	SET		terms.ReplacedValue = NULL
	FROM	cs_SiteTerms AS terms
	WHERE	terms.[Language] = @Language
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_SiteTerms_ResetAllToDefault] TO PUBLIC
GO

Print 'Creating...GetProperties'

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProperties]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetProperties]
GO

CREATE FUNCTION [dbo].[GetProperties] ( @propertyNames nvarchar(max), @propertyValues nvarchar(max) )
RETURNS @properties TABLE ( PropertyName nvarchar(256), PropertyValue nvarchar(max) )
AS
BEGIN

	IF (@propertyNames IS NULL OR LEN(RTRIM(@propertyNames)) = 0)
		RETURN

	DECLARE @id int, @delimiter nvarchar(1)
	DECLARE @propertyName nvarchar(256), @propertyType nvarchar(10), @valueIndex int, @valueLength int, @count int

	SET @delimiter = ':'
	SET @count = 0

	DECLARE @propertyDef TABLE
	(
		id int identity(1,1),
		PropertyName nvarchar(256),
		PropertyType nvarchar(10),
		ValueIndex int,
		ValueLength int
	)

	WHILE(CHARINDEX(@delimiter, @propertyNames) > 0)
	BEGIN
		SET @count = @count + 1
		IF (@count = 1)
			SET @propertyName = SUBSTRING(@propertyNames, 1, CHARINDEX(@delimiter, @propertyNames) - 1)
		ELSE IF (@count = 2)
			SET @propertyType = SUBSTRING(@propertyNames, 1, CHARINDEX(@delimiter, @propertyNames) - 1)
		ELSE IF (@count = 3)
			SET @valueIndex = CONVERT(int, SUBSTRING(@propertyNames, 1, CHARINDEX(@delimiter, @propertyNames) - 1))
		ELSE IF (@count = 4)
		BEGIN
			SET @valueLength = CONVERT(int, SUBSTRING(@propertyNames, 1, CHARINDEX(@delimiter, @propertyNames) - 1))
			SET @count = 0
			INSERT INTO @propertyDef ( PropertyName, PropertyType, ValueIndex, ValueLength ) VALUES ( @propertyName, @propertyType, @valueIndex, @valueLength )
		END
		SET @propertyNames = SUBSTRING(@propertyNames, CHARINDEX(@delimiter, @propertyNames) + 1, LEN(@propertyNames))
	END

	INSERT INTO @properties ( PropertyName, PropertyValue )
	SELECT	PropertyName, PropertyValue = RTRIM(SUBSTRING(@propertyValues, ValueIndex + 1, ValueLength))
	FROM	@propertyDef

	RETURN
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT SELECT ON [dbo].[GetProperties] TO [public]
GO


IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE [name] = 'cs_Messaging_MessageReplies')
	BEGIN
		EXEC sp_executesql N'
		CREATE TABLE [dbo].[cs_Messaging_MessageReplies]
		(			
			ParentMessageId UNIQUEIDENTIFIER NOT NULL,		
			MessageId UNIQUEIDENTIFIER NOT NULL,			

			CONSTRAINT PK_cs_Messaging_MessageReplies 
				PRIMARY KEY CLUSTERED (ParentMessageId, MessageId) WITH FILLFACTOR = 90,
			
			CONSTRAINT FK_cs_Messaging_MessageReplies_MessageId_cs_Messaging_Messages_MessageId 
				FOREIGN KEY (MessageId) REFERENCES cs_Messaging_Messages (MessageId),

			CONSTRAINT FK_cs_Messaging_MessageReplies_ParentMessageId_cs_Messaging_Messages_MessageId 
				FOREIGN KEY (ParentMessageId) REFERENCES cs_Messaging_Messages (MessageId),

			CONSTRAINT IX_cs_Messaging_MessageReplies_MessageId 
				UNIQUE(MessageId)
		)'
	END
	

HotfixCreateWithSchema.sql

Print 'Creating...te_DistributedMonitor_Leases_Remove'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[te_DistributedMonitor_Leases_Remove]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[te_DistributedMonitor_Leases_Remove]
GO

CREATE PROCEDURE [dbo].te_DistributedMonitor_Leases_Remove
(
	@OlderThanHours	INT
)
AS
BEGIN
	SET NOCOUNT ON

	BEGIN TRAN

	DELETE	te_DistributedMonitor_Leases WITH (TABLOCKX)
	WHERE	(-DATEDIFF(HOUR, GETUTCDATE(), LastStatusUtc)) > @OlderThanHours

	COMMIT TRAN
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].te_DistributedMonitor_Leases_Remove to public
go