web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Cannot Sync NAV Tenant...
Microsoft Dynamics NAV (Archived)

Cannot Sync NAV Tenant - Table objects deleted from SQL database & NAV development environment

(0) ShareShare
ReportReport
Posted on by Community Member

Dear NAV experts,

I am trying to go through the upgrade process (in a sandbox environment of course). I have run into an issue. Several tables in the customer's old database are legacy objects from an older version of NAV (these tables are not found in target version of NAV). I checked SQL and there are no records in any of these tables i.e. the tables have never been used.

I figured I might as well just delete these tables from the SQL database using SQL Server Management Studio (and I did this). Now, I cannot synchronize the schema with validation and I cannot even synchronize the schema with force. I get an error "The following error was unexpected: Invalid Object Name" if I try to synchronize the schema with force and I get the message "The schema synchronization may result in deleted data" if I try to synchronize with validation or check only. Actually, this message is quite absurd because there was never any data records in these tables.

In fact, the objects have been deleted from both the NAV development environment and they have also been deleted from SQL Server. I have restarted the service for both the NAV Server and the SQL server (didn't help).

I also cannot run the upgrade process because I get the message "Data upgrade cannot be started in the current tenant state: 'Operational with sync failure'.

What can I do to get the schema to sync and continue the upgrade process?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Mohana Yadav Profile Picture
    Mohana Yadav 61,003 Super User 2025 Season 2 on at

    you shouldn't have deleted from SQL.

    Please restore the backup, if you have, before deleting the tables from SQl and try again

  • Verified answer
    Jens Glathe Profile Picture
    Jens Glathe 6,092 on at

    Hi there,

    you can add code in the upgrade codeunit for this. "Sync-NAVTenant -mode CheckOnly" should give you the template for it. It should go to the TableSyncSetup type function in the upgrade codeunit.

    For example:

    DataUpgradeMgt.SetTableSyncSetup(<your tablenumber here>,0,TableSynchSetup.Mode::Force);

    This should help, but I'm not sure if it tolerates deletion by SQL (these tables already have the "new" state, not the "old" that requires to be synced). The code, however, would work on your next sandbox run.

    How to get out of the hole you're in now: You can try to really delete the tables. This can be quite tricky, but since you managed to do it already, all you've got left is some metadata. This script here might help:

    /*
This script walks the table [dbo].[Tables marked for deletion], drops these tables from the database, and 
removes the objects from the database. This works regardless of the license permissions.
!!! Caution !!! Inapropriate use of this script will destroy a NAV database.
*/
begin transaction cleanup

declare @TableID int
declare @TableID2 int
declare @ViewID int
declare @ViewName nvarchar(250)
declare @TableName varchar(250)

set rowcount 0
select * into #TablesToDelete from [dbo].[Tables marked for deletion]

set rowcount 1
select 
	@TableID = [TableNo],
    @TableName = [SQLTableName] 
	from #TablesToDelete
	order by [TableNo]
select @TableID2 = 0

while @@ROWCOUNT <> 0
begin
	set rowcount 0

	/* find linked VSIFT views and delete them with a subloop */
	select * into #ViewsToDelete 
		from sys.objects 
		where type = 'V'
		and name like substring(@TableName,8,datalength(@TableName)-8) + '$%'
	set rowcount 1
	select 
		@ViewID = object_id,
		@ViewName = N'[dbo].[' + name + N']'
		from #ViewsToDelete
	while @@ROWCOUNT <> 0
	begin
		set rowcount 0		
		print ''
		print 'drop view ' + @ViewName
		exec('drop view ' + @ViewName)	  
		delete
			from #ViewsToDelete
			where object_id = @ViewID
		set rowcount 1
		select 
			@ViewID = object_id,
			@ViewName = N'[dbo].[' + name + N']'
			from #ViewsToDelete
	end;
	set rowcount 0
	drop table #ViewsToDelete

	print ''
	print 'drop table ' + @TableName
	exec('drop table ' + @TableName)

	if @TableID <> @TableID2
	begin
		delete 
			from [dbo].[Object] 
			where Type < 2 and ID = @TableID
		delete 
			from [dbo].[Object Metadata]
			where [Object Type] < 2 and [Object ID] = @TableID
		delete 
			from [dbo].[Object Tracking]
			where [Object Type] < 2 and [Object ID] = @TableID
		delete 
			from [dbo].[Object Translation]
			where [Object Type] < 2 and [Object ID] = @TableID
        select @TableID2 = @TableID
	end

	delete 
		from #TablesToDelete 
		where [SQLTableName] = @TableName
	set rowcount 1
	select 
		@TableID = [TableNo],
		@TableName = [SQLTableName] 
		from #TablesToDelete
		order by [TableNo]
end
set rowcount 0
drop table #TablesToDelete
commit transaction cleanup

    The whole package of useful scripts and accompanying objects can be downloaded here. With it you should be able to:

    - import it into your already converted database (use the .fob with suffix "2013"),
    - define the tables you have deleted for the script,
    - run the script to completely remove the metadata from the database.

    Afterwards, the synchronization should run.

    with best regards


    Jens

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans