Dynamics NAV 7 Beta | Error when upgrading

When upgrading from a previous build of NAV7 beta to the current build you might get this error.

It occurs after the conversion of the database.

For some reason this field is changed from length 252 to 504.

To solve this you must manually change the length to 504.

This can only be done via SQL and the table should be empty.

Since we need the permissions, we can store them into a copy table. Hence we create this  

CREATE TABLE [dbo].[Permission2](

       [timestamp] [timestamp] NOT NULL,

       [Role ID] [nvarchar](20) NOT NULL,

       [Object Type] [int] NOT NULL,

       [Object ID] [int] NOT NULL,

       [Read Permission] [int] NOT NULL,

       [Insert Permission] [int] NOT NULL,

       [Modify Permission] [int] NOT NULL,

       [Delete Permission] [int] NOT NULL,

       [Execute Permission] [int] NOT NULL,

       [Security Filter] [varbinary](252) NOT NULL,

 CONSTRAINT [Permission$0] PRIMARY KEY CLUSTERED

(

       [Role ID] ASC,

       [Object Type] ASC,

       [Object ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]

) ON [Data Filegroup 1]

 

GO

And copy the data. 

INSERT INTO [dbo].[Permission2]

           ([Role ID]

           ,[Object Type]

           ,[Object ID]

           ,[Read Permission]

           ,[Insert Permission]

           ,[Modify Permission]

           ,[Delete Permission]

           ,[Execute Permission]

           ,[Security Filter])

     SELECT

            [Role ID],

            [Object Type],

            [Object ID],

            [Read Permission],

            [Insert Permission],

            [Modify Permission],

            [Delete Permission],

            [Execute Permission],

            [Security Filter] from [Permission]

GO

Then we drop the table

DROP TABLE [dbo].[Permission]

And create a new one WITH length 504

CREATE TABLE [dbo].[Permission](

       [timestamp] [timestamp] NOT NULL,

       [Role ID] [nvarchar](20) NOT NULL,

       [Object Type] [int] NOT NULL,

       [Object ID] [int] NOT NULL,

       [Read Permission] [int] NOT NULL,

       [Insert Permission] [int] NOT NULL,

       [Modify Permission] [int] NOT NULL,

       [Delete Permission] [int] NOT NULL,

       [Execute Permission] [int] NOT NULL,

       [Security Filter] [varbinary](504) NOT NULL,

 CONSTRAINT [Permission2$0] PRIMARY KEY CLUSTERED

(

       [Role ID] ASC,

       [Object Type] ASC,

       [Object ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]

) ON [Data Filegroup 1]

And copy the data back

INSERT INTO [dbo].[Permission]

           ([Role ID]

           ,[Object Type]

           ,[Object ID]

           ,[Read Permission]

           ,[Insert Permission]

           ,[Modify Permission]

           ,[Delete Permission]

           ,[Execute Permission]

           ,[Security Filter])

     SELECT

            [Role ID],

            [Object Type],

            [Object ID],

            [Read Permission],

            [Insert Permission],

            [Modify Permission],

            [Delete Permission],

            [Execute Permission],

            [Security Filter] from [Permission2]

GO

And drop the temporary table

DROP TABLE [dbo].[Permission2]

And the conversion runs...

Cheers.

 

 

 

Comment List
Related
Recommended