Hi,
I tried to run script. But it is giving error on one line and fails to create table. So it fails to insert other entries related to that table.
Also after I get sucessfully executed script, will my DNN will fine after upgrade. Right now, after upgrading I am getting error for all pages.
Here is the script where I am getting error.
Getting error on line - VALUES (P.PortalID, @SettingName, @DefaultValue, @UserID, GetDate(), @UserID, GetDate(), P.DefaultLanguage)
Full script -
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]
@SettingName nVarChar( 50), -- not Null or Empty
@DefaultValue Int, -- Null: remove invalid settings only
@ForceDefault Bit, -- replace existing non-empty value?
@MinValue Int = Null, -- optional: specify min value, smaller values will be adjusted to default
@MaxValue Int = Null, -- optional: specify max value, smaller values will be adjusted to default
@UserID Int = -1 -- ID of executing user
AS
BEGIN
DECLARE @DefaultString AS nVarChar( 11) = CAST(@DefaultValue AS nVarChar(11));
MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN
INSERT ( PortalID, SettingName, SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate, CultureCode )
VALUES (P.PortalID, @SettingName, @DefaultValue, @UserID, GetDate(), @UserID, GetDate(), P.DefaultLanguage)
WHEN MATCHED AND @DefaultValue Is Null
AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
OR {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) < @MinValue
OR {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) > @MaxValue) THEN
DELETE
WHEN MATCHED AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
OR {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) < @MinValue
OR {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) > @MaxValue
OR @ForceDefault = 1) THEN
UPDATE SET SettingValue = @DefaultString, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
Thanks,
Sandeep
|