Hi Peter,

I'm having problems upgrading from 3.4.11 to DMX 5.3.8. The site in question has been upgraded from DNN484 to DNN561 and I've had to use SQLCompare to remove a number of collation conflicts along the way.

Having upgraded DNN to 561, I then attempted to install DMX 538. The installation failed when executing the 5.0.2 sql provider due to to collation conflicts. I corrected the conflicts by modifying the 5.0.2 sql provider script. I tested the script for syntax errors and then rolled back the site and attempted to install DMX 538 with the modified PA. The installation again failed at the 5.0.2 stage. The messages given are included below and seem to be divided into a group of syntax errors followed by sql related errors.

The revised SQL provider is included below the error.

Please can you advise how I might get passed this particular error.



Thanks in advance

Phil Biggs


Start Sql execution: 05.00.02.SqlDataProvider file
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near 'E'. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.a.t.h.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.S.e.n.t.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.a.t.h.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.a.t.h.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.e.d.B.y.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.e.d.U.n.t.i.l.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.K.e.y.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.e.d.B.y.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.e.d.U.n.t.i.l.' is an invalid name because it contains a NULL character or an invalid unicode character. '.L.o.c.k.K.e.y.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.T.e.x.t.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.c.t.i.o.n.' is an invalid name because it contains a NULL character or an invalid unicode character. '.D.a.t.i.m.e.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.A.l.l.o.w.A.c.c.e.s.s.' is an invalid name because it contains a NULL character or an invalid unicode character. '.E.n.t.r.y.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.e.r.m.i.s.s.i.o.n.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.P.o.r.t.a.l.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.R.o.l.e.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. '.U.s.e.r.I.d.' is an invalid name because it contains a NULL character or an invalid unicode character. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute @AttributeId Int, @PortalId int AS SELECT a.* FROM {databaseOwner}{objectQualifier}DMX_Attributes a WHERE a.AttributeId = @AttributeId AND (a.[PortalId] = @PortalId OR @PortalId=-1) GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry @PortalId Int, @EntryId Int, @NewCollectionId INT AS DECLARE @destpath VARCHAR(2000) SET @destpath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId = @NewCollectionId) IF NOT @destpath LIKE ';'+CAST(@EntryId AS VARCHAR(10))+';' UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET CollectionId=@NewCollectionId WHERE LastVersionId=@EntryId AND PortalId=@PortalId GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUsersToNotify') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify @PortalId int AS SELECT u.* FROM {databaseOwner}{objectQualifier}Users u WHERE (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Notifications n WHERE n.[UserId] = u.UserId AND n.[PortalId] = @PortalId AND n.[Sent] IS NULL)>0 GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetLastModified') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId Int, @EntryId Int AS DECLARE @entrypath VARCHAR(2000) SET @entrypath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId = @EntryId) UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastModified=GETDATE() WHERE PortalId=@PortalId AND @entrypath LIKE [Path]+'%' AND EntryId=LastVersionId GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_LockEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry @PortalId Int, @EntryId Int, @LockedUntil DateTime, @LockedBy Int, @LockKey UNIQUEIDENTIFIER AS DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries; UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET [LockedBy] = @LockedBy, [LockedUntil] = @LockedUntil, [LockKey] = @LockKey WHERE ([EntryId] = @EntryId OR LastVersionId=@EntryId) AND [PortalId] = @PortalId; ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries; GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UnLockEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry @PortalId Int, @EntryId Int AS DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries; UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET [LockedBy] = -1, [LockedUntil] = GETDATE(), [LockKey] = NULL WHERE ([EntryId] = @EntryId OR LastVersionId=@EntryId) AND [PortalId] = @PortalId; ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries; GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{objectQualifier}DMX_Split') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION {databaseOwner}{objectQualifier}DMX_Split GO CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_Split (@s varchar(512), @sep char(1)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddFolder') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder @PortalId INT, @CollectionId INT=0, @FolderName NVARCHAR(255)='New Folder', @ViewRoles VARCHAR(2000)='', @EditRoles VARCHAR(2000)='', @AddRoles VARCHAR(2000)='', @ApproveRoles VARCHAR(2000)='' AS BEGIN DECLARE @Owner INT SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId) DECLARE @NewId INT INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin) VALUES (@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0); SET @NewId = SCOPE_IDENTITY() UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastVersionId=EntryId WHERE EntryId=@NewId; INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts (Locale, ObjectId, SubTypeKey, [Text], TypeKey) VALUES ('', @NewId, 'TITLE', @FolderName, 'ENT'); INSERT INTO {databaseOwner}{objectQualifier}DMX_Log ([Action],[Datime],[EntId],[UserId]) VALUES ('Add', GETDATE(), @NewId, @Owner); DECLARE @AdminRoleId INT SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId) IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles='' BEGIN IF @CollectionId=0 BEGIN -- set Admin roles for default folder under root INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Permissions WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD'); END ELSE -- Inherit permissions from parent INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId] FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE EntryId=@CollectionId; END ELSE BEGIN -- set View roles INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1 INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS = spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS) INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW') WHERE NOT @ViewRoles='' AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10); -- set Edit roles INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1 INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS) INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT') WHERE NOT @EditRoles='' AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10); -- set Add roles INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1 INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS) INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD') WHERE NOT @AddRoles='' AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10); -- set Approve roles INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1 INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS) INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE') WHERE NOT @ApproveRoles='' AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10); -- make sure Admin is part of this INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId]) SELECT DISTINCT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10 FROM {databaseOwner}{objectQualifier}DMX_Permissions p WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD') AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId); END END GO IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryPermissionMaintenance') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance GO CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance @PortalId INT AS DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.EntryId={databaseOwner}{objectQualifier}DMX_EntryPermissions.EntryId AND e.PortalId=@PortalId) AND ((UserId>0 AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users u INNER JOIN {databaseOwner}{objectQualifier}UserPortals up ON u.UserId=up.UserId WHERE u.UserId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId AND up.PortalId=@PortalId)) OR (UserId=-10 AND RoleId>0 AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r WHERE r.RoleId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId AND r.PortalId=@PortalId))) GO

Info End Sql execution: 05.00.02.SqlDataProvider file
Info Finished Sql execution
Failure Installation Failed - Script
Info Installation Failed - Bring2mind\DMX
Info Deleted temporary install folder


Modified Provider (collations added in area of DMX_EntryPermissions):

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
@AttributeId Int,
@PortalId int
AS

SELECT
a.*
FROM
{databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
a.AttributeId = @AttributeId
AND (a.[PortalId] = @PortalId OR @PortalId=-1)
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
@PortalId Int,
@EntryId Int,
@NewCollectionId INT
AS

DECLARE @destpath VARCHAR(2000)
SET @destpath = (SELECT [Path]
FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE EntryId = @NewCollectionId)
IF NOT @destpath LIKE ';'+CAST(@EntryId AS VARCHAR(10))+';'
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET CollectionId=@NewCollectionId
WHERE
LastVersionId=@EntryId
AND PortalId=@PortalId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetUsersToNotify') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
@PortalId int
AS

SELECT
u.*
FROM
{databaseOwner}{objectQualifier}Users u
WHERE
(SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Notifications n WHERE n.[UserId] = u.UserId AND n.[PortalId] = @PortalId AND n.[Sent] IS NULL)>0

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetLastModified') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified
@PortalId Int,
@EntryId Int
AS

DECLARE @entrypath VARCHAR(2000)
SET @entrypath = (SELECT [Path]
FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE EntryId = @EntryId)

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET LastModified=GETDATE()
WHERE PortalId=@PortalId
AND @entrypath LIKE [Path]+'%'
AND EntryId=LastVersionId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_LockEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry
@PortalId Int,
@EntryId Int,
@LockedUntil DateTime,
@LockedBy Int,
@LockKey UNIQUEIDENTIFIER
AS

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
[LockedBy] = @LockedBy,
[LockedUntil] = @LockedUntil,
[LockKey] = @LockKey
WHERE
([EntryId] = @EntryId OR LastVersionId=@EntryId)
AND [PortalId] = @PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UnLockEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry
@PortalId Int,
@EntryId Int
AS

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
[LockedBy] = -1,
[LockedUntil] = GETDATE(),
[LockKey] = NULL
WHERE
([EntryId] = @EntryId OR LastVersionId=@EntryId)
AND [PortalId] = @PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{objectQualifier}DMX_Split') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_Split
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_Split (@s varchar(512), @sep char(1))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddFolder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
@PortalId INT,
@CollectionId INT=0,
@FolderName NVARCHAR(255)='New Folder',
@ViewRoles VARCHAR(2000)='',
@EditRoles VARCHAR(2000)='',
@AddRoles VARCHAR(2000)='',
@ApproveRoles VARCHAR(2000)=''
AS

BEGIN
DECLARE @Owner INT
SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
DECLARE @NewId INT
INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries
(CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin)
VALUES
(@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0);
SET @NewId = SCOPE_IDENTITY()
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET LastVersionId=EntryId
WHERE EntryId=@NewId;
INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts
(Locale, ObjectId, SubTypeKey, [Text], TypeKey)
VALUES ('', @NewId, 'TITLE', @FolderName, 'ENT');
INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
([Action],[Datime],[EntId],[UserId])
VALUES ('Add', GETDATE(), @NewId, @Owner);
DECLARE @AdminRoleId INT
SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles=''
BEGIN
IF @CollectionId=0
BEGIN
-- set Admin roles for default folder under root
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Permissions
WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD');
END
ELSE
-- Inherit permissions from parent
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId]
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE EntryId=@CollectionId;
END
ELSE
BEGIN
-- set View roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS = spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW')
WHERE NOT @ViewRoles=''
AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
-- set Edit roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT')
WHERE NOT @EditRoles=''
AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
-- set Add roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD')
WHERE NOT @AddRoles=''
AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
-- set Approve roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10)) collate Latin1_General_CI_AS =spl1.s collate Latin1_General_CI_AS OR r1.Rolename=spl1.s collate Latin1_General_CI_AS)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE')
WHERE NOT @ApproveRoles=''
AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
-- make sure Admin is part of this
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT DISTINCT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Permissions p
WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD')
AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId);
END
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryPermissionMaintenance') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
@PortalId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.EntryId={databaseOwner}{objectQualifier}DMX_EntryPermissions.EntryId AND e.PortalId=@PortalId)
AND ((UserId>0
AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users u
INNER JOIN {databaseOwner}{objectQualifier}UserPortals up ON u.UserId=up.UserId
WHERE u.UserId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId
AND up.PortalId=@PortalId))
OR (UserId=-10
AND RoleId>0
AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r
WHERE r.RoleId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId
AND r.PortalId=@PortalId)))

GO

Ok, I solved the syntax errors at the start of the above error message. This was due to the modified SQL provider not being saved in UTF8 format. Changing this and running the install on the rolled back site then revealed the next problem:

Start Sql execution: 05.02.00.SqlDataProvider file


The installation failed.

Again, running the provider script via the DNN SQl facility is successful.

Any clues on how to diagnose the Index related error and the collation issues?

For the first report: indeed an encoding issue. Probably a result of your editor when you edited. I always use Notepad++ which allows me to see the encoding and change if needed.

For the second issue: isn't the index issue related to the collation? I.e. the collation error means the change to the column doesn't happen and then the subsequent error is produced.
I can't honestly explain why the installer would do things differently than directly in Host > SQL. If you have any places for me to look I'll gladly do that.

Final note: if all else fails you can install manually. That is: run update scripts by hand through Host > SQL and then make sure to update the version nr in DesktopModules and Packages. That will "fool" DNN into believing a more recent version was installed. Then, once confident that the last scripts will run OK, just install the latest version.

Peter

Thanks for the reply Peter. For manual install, do you mean run all sql providers from 3.4.11 - 5.3.8 sequentially, update, DesktopModules and Packages and then copy over the files?

Please ignore my last post.

I've resolved the collation conflicts that occur when the 5.2.0 sql provider runs. This also showed that the index problem was not related. Now when the 5.2.0 provider runs I get just the index problem:

System.Data.SqlClient.SqlException: The index 'IX_DMX_Entries_Data1' is dependent on column 'Entry'. ALTER TABLE ALTER COLUMN Entry failed because one or more objects access this column. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) ALTER TABLE dbo.DMX_Entries ALTER COLUMN Entry NVARCHAR(2000)

And the code causing this is:

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
ALTER COLUMN Entry NVARCHAR(2000)
GO

Is the index 'IX_DMX_Entries_Data1' essential...could it be dropped to allow the script to run or would it cause problems further down the line?

How should I go about tackling this?

Cheers

Phil

Hi Phil,

No that index is not essential. Plus: I can't remember having added that one either. Maybe an optimization script created that on your server, not sure. Try deleting it and running again.

Peter

Hi Peter,

I modified the 5.2.0 provider so that the index 'IX_DMX_Entries_Data1 was dropped before attempting to alter the DMX_Entries table. I then recreated the INdex. The 5.3.8 install script then ran 100% cleanly.

When trying to access the module, the follwing error was thwon:


DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 83969dfd-9c42-4229-bc0d-4d3c7614992a
InnerException: The type initializer for 'Bring2mind.DNN.Modules.DMX.Data.DataProvider' threw an exception.

This was cured by deleting Bring2mind.DNN.Modules.DMX.SqlDataProvider.dll as described in another post. Also I had to delete Bring2mind.ComponentArt.Web.UI.dll to get the module to display correctly.

Hopefully things should be ok now, but before I run dmx3-4 and re-indexing scripts, I have one more question. The folder containing all of the documents managed by DMX has been moved to a different location. How can I tell DMX to look in the new location ?

Thanks for your help.

Phil

Great ...almost there.

The location of the files is now on the "Storage Provider" screen. Find that on the Admin menu of DMX. You'll see that it is set to File based storage and there you speficy the base path. Please make sure permissions are set correctly on that folder and files to allow the asp.net application to make all necessary changes.

The location should not impact the upgrade script in any way, BTW. But it will be essential for being able to download the old content.

Peter

Hi Peter,

All sorted now - thanks

Phil