/*
   AUTHOR: Charlene van Heerden
   DATE: 2016-07-12
   DESCRIPTION: Task Description - Change data type from varchar(8000) to text to accommodate Rad Editor text 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_pal_Task
	(
	recId int NOT NULL IDENTITY (1, 1),
	taskTypeId int NULL,
	taskCategoryId int NULL,
	name varchar(150) NULL,
	createdDate datetime NULL,
	updatedDate datetime NULL,
	initiatorUserId int NULL,
	assigneeUserId int NULL,
	taskStatusId int NULL,
	dueDate datetime NULL,
	description text NULL,
	canvasId int NULL,
	taskSnapshotId int NULL,
	taskSnapshotPosX int NULL,
	taskSnapshotPosY int NULL,
	taskNumber varchar(50) NULL,
	taskPriorityId int NULL,
	customerCode varchar(10) NULL,
	projectCode varchar(10) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_pal_Task SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_pal_Task ON
GO
IF EXISTS(SELECT * FROM dbo.pal_Task)
	 EXEC('INSERT INTO dbo.Tmp_pal_Task (recId, taskTypeId, taskCategoryId, name, createdDate, updatedDate, initiatorUserId, assigneeUserId, taskStatusId, dueDate, description, canvasId, taskSnapshotId, taskSnapshotPosX, taskSnapshotPosY, taskNumber, taskPriorityId, customerCode, projectCode)
		SELECT recId, taskTypeId, taskCategoryId, name, createdDate, updatedDate, initiatorUserId, assigneeUserId, taskStatusId, dueDate, CONVERT(text, description), canvasId, taskSnapshotId, taskSnapshotPosX, taskSnapshotPosY, taskNumber, taskPriorityId, customerCode, projectCode FROM dbo.pal_Task WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_pal_Task OFF
GO
DROP TABLE dbo.pal_Task
GO
EXECUTE sp_rename N'dbo.Tmp_pal_Task', N'pal_Task', 'OBJECT' 
GO
ALTER TABLE dbo.pal_Task ADD CONSTRAINT
	PK_pal_Task PRIMARY KEY CLUSTERED 
	(
	recId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_Task_CanvasId ON dbo.pal_Task
	(
	canvasId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT




BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_pal_TaskHistory
	(
	recId int NOT NULL IDENTITY (1, 1),
	taskId int NULL,
	changeNum int NULL,
	changeDate datetime NULL,
	changeType varchar(10) NULL,
	userId int NULL,
	taskTypeId int NULL,
	taskCategoryId int NULL,
	name varchar(150) NULL,
	createdDate datetime NULL,
	updatedDate datetime NULL,
	initiatorUserId int NULL,
	assigneeUserId int NULL,
	taskStatusId int NULL,
	dueDate datetime NULL,
	description text NULL,
	canvasId int NULL,
	taskSnapshotId int NULL,
	taskSnapshotPosX int NULL,
	taskSnapshotPosY int NULL,
	taskNumber varchar(50) NULL,
	taskPriorityId int NULL,
	projectCode varchar(10) NULL,
	customerCode varchar(10) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_pal_TaskHistory SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_pal_TaskHistory ON
GO
IF EXISTS(SELECT * FROM dbo.pal_TaskHistory)
	 EXEC('INSERT INTO dbo.Tmp_pal_TaskHistory (recId, taskId, changeNum, changeDate, changeType, userId, taskTypeId, taskCategoryId, name, createdDate, updatedDate, initiatorUserId, assigneeUserId, taskStatusId, dueDate, description, canvasId, taskSnapshotId, taskSnapshotPosX, taskSnapshotPosY, taskNumber, taskPriorityId, projectCode, customerCode)
		SELECT recId, taskId, changeNum, changeDate, changeType, userId, taskTypeId, taskCategoryId, name, createdDate, updatedDate, initiatorUserId, assigneeUserId, taskStatusId, dueDate, CONVERT(text, description), canvasId, taskSnapshotId, taskSnapshotPosX, taskSnapshotPosY, taskNumber, taskPriorityId, projectCode, customerCode FROM dbo.pal_TaskHistory WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_pal_TaskHistory OFF
GO
DROP TABLE dbo.pal_TaskHistory
GO
EXECUTE sp_rename N'dbo.Tmp_pal_TaskHistory', N'pal_TaskHistory', 'OBJECT' 
GO
ALTER TABLE dbo.pal_TaskHistory ADD CONSTRAINT
	PK_pal_TaskHistory PRIMARY KEY CLUSTERED 
	(
	recId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_TaskHistory_TaskId ON dbo.pal_TaskHistory
	(
	taskId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_TaskHistory_CanvasId ON dbo.pal_TaskHistory
	(
	canvasId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
