Access Control table error during Upgrade NAV2009 R2>NAV2013

mahesh

Hello NAV experts,

As per upgrade steps, I'm opening NAV2009 R2 database with NAV2013 client after completing Upgrade Step-1.
The moment i open 2009 R2 Database with 2013 clients, it gives me below error after few seconds.

---

Microsoft Dynamics NAV Development Environment

---

The following SQL Server error or errors occurred when accessing the Access Control table:

5074,"42000",[Microsoft][SQL Server Native Client 10.0][SQL Server]The statistics 'Company Name' is dependent on column 'Company Name'.
4922,"42000",[Microsoft][SQL Server Native Client 10.0][SQL Server]ALTER TABLE ALTER COLUMN Company Name failed because one or more objects access this column.

SQL:
ALTER TABLE [UAT_NAV2013].[dbo].[Access Control] ALTER COLUMN [Company Name] NVARCHAR(30) NOT NULL

---

OK

---

I don't have "Access Control" table in SQL database also because it is introduced in 2013 - i guess.
As part of client exe upgrade, system internally processing & giving me this error.

Any idea and solution?

Mahesh Jain

Marije_Brummel

Chances are you ran some SQL script like sp_createstats to create statistics on SQL Server.

NAV does not check that since running these scripts are not recommended by Microsoft.

To remove the statistics you can run this script (at own risk)

After running the script you need to either re-enable the auto create and auto update statistics and/or run sp_createstats again.

set statistics io off 
set nocount off 
 
declare @id int, @name varchar(128), @statement nvarchar(1000) 
declare stat_cur cursor fast_forward for 
select [id], [name] from sysindexes 
where (indexproperty([id], [name], N'IsStatistics') = 1) 
and (isnull(objectproperty([id], N'IsUserTable'),0) = 1) 
order by object_name([id]) 
open stat_cur 
fetch next from stat_cur into @id, @name 
while @@fetch_status = 0 begin 
set @statement = 'DROP STATISTICS [' + object_name(@id) + '].[' + @name + ']' 
begin transaction 
print @statement 
exec sp_executesql @statement 
commit transaction 
fetch next from stat_cur into @id, @name 
end 
close stat_cur 
deallocate stat_cur

Thanks to Jorg Stryk for the script.