I think I solved the problem. Please let me know if I overdid something. Thank you - Subodh
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TabModuleSettings
DROP CONSTRAINT FK_TabModuleSettings_TabModules
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TabModuleSettings
(
TabModuleID int NOT NULL,
SettingName nvarchar(50) NOT NULL,
SettingValue ntext NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TabModuleSettings)
EXEC('INSERT INTO dbo.Tmp_TabModuleSettings (TabModuleID, SettingName, SettingValue)
SELECT TabModuleID, SettingName, CONVERT(ntext, SettingValue) FROM dbo.TabModuleSettings WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TabModuleSettings
GO
EXECUTE sp_rename N'dbo.Tmp_TabModuleSettings', N'TabModuleSettings', 'OBJECT'
GO
ALTER TABLE dbo.TabModuleSettings ADD CONSTRAINT
PK_TabModuleSettings PRIMARY KEY CLUSTERED
(
TabModuleID,
SettingName
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TabModuleSettings WITH NOCHECK ADD CONSTRAINT
FK_TabModuleSettings_TabModules FOREIGN KEY
(
TabModuleID
) REFERENCES dbo.TabModules
(
TabModuleID
) ON UPDATE NO ACTION
ON DELETE CASCADE
NOT FOR REPLICATION
GO
COMMIT
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[UpdateModuleSetting]
@ModuleId int,
@SettingName nvarchar(50),
@SettingValue ntext
as
update dbo.ModuleSettings
set SettingValue = @SettingValue
where ModuleId = @ModuleId
and SettingName = @SettingName
GO
ALTER procedure [dbo].[UpdateTabModuleSetting]
@TabModuleId int,
@SettingName nvarchar(50),
@SettingValue ntext
as
update dbo.TabModuleSettings
set SettingValue = @SettingValue
where TabModuleId = @TabModuleId
and SettingName = @SettingName
GO